Cannot insert duplicates

[misstj555]

Hello. I have a quick question. I am making a database as a personal project (I am using Microsoft SQL Server therefore I am using its SQL syntax). All it does is that it keeps track of singing groups and bands, the members in these groups, albums they made, songs they did and awards they won (for example, I can insert The Beatles into the Database and record all their members, when they debuted, when they disbanded, their songs and more).

For now I am only going to create a table that can store the group's/band's name and another table to store the band's/group's members. When this works then I will add more tables. Here is the code for this:

USE BandD_Base 
GO
CREATE TABLE BAND(   
BandName                 Char(100)                     NOT NULL,   
BandLocation             Char(100)                     NOT NULL,
DebuteDate               Numeric(4)                    NOT NULL,
DisbandDate              Numeric(4)                    NULL,
Genre                    Char(100)                     NOT NULL,
    CONSTRAINT             Band_PK             PRIMARY KEY(BandName), 
    CONSTRAINT             CheckDebute         CHECK 
                                                (DebuteDate LIKE '[1-2][0-9][0-9][0-9]'),
    CONSTRAINT              CheckDisband       CHECK 
                                                 (DisbandDate LIKE '[1-2][0-9][0-9][0-9]'),

CREATE TABLE MEMBERS( 
    MemberName            Char(100)                     NOT NULL, 
    Nationality           Char(100)                     NOT NULL,
    Position              Char(100)                     NOT NULL,
    LeftGroup             Char(3)                       NOT NULL,
    BandName              Char(100)                     NOT NULL,   
        CONSTRAINT          Member_PK          PRIMARY KEY(MemberName), 
        CONSTRAINT          CheckLeftGroup     CHECK 
                                                                (LeftGroup IN ('Yes', 'No')),
        CONSTRAINT          Member_FK          FOREIGN KEY (BandName) 
            REFERENCES BAND(BandName));

Here is some sample data about an American girl group called Destiny's Child. It might not be completely accurate but I am just using this information for fun. Here is the SQL code for it:

INSERT INTO BAND(BandName, BandLocation, DebuteDate, DisbandDate, Genre) VALUES  
('Destinys Child', 'America', '1990', '2006', 'R&B');

INSERT INTO MEMBERS(MemberName, Nationality, Position, LeftGroup, BandName) VALUES  
('Beyoncé Knowles', 'American', 'Vocals', 'No', 'Destinys Child'),
('Kelly Rowland', 'American', 'Vocals', 'No', 'Destinys Child'),
('Michelle Williams', 'American', 'Vocals', 'No', 'Destinys Child'),
('LaTavia Roberson', 'American', 'Vocals', 'Yes', 'Destinys Child'),
('LeToya Luckett', 'American', 'Vocals', 'Yes', 'Destinys Child'),
('Farrah Franklin', 'American', 'Vocals', 'Yes', 'Destinys Child'),

I get an error that says that I cannot insert duplicates. I know that the error is coming from the fact that when I inserted all the members of Destiny's Child, I had to input "Destiny's Child" for the band name or else they will not be associated with the same group. I just don't know how to fix this.

Is it because I used the band name as the primary key? Should I make a BandID that will be a surrogate key and make that the primary key (I would think that I will get the same error)? I know there are no multi-valued dependencies because one band can have many members so this is a one-to-many relationship (therefore I don't need to normalize anything).

I'm sure the answer is probably really simple but any help is appreciated. Thanks.

[Reverend Jim]

For starters, the constraint you have for DebutDate and DisbandDate is for a character field and your fields are numeric. I'm testing it on sqlite3 so it may be different in MsSql. I'll have a look at the rest in a bit.

[Reverend Jim]

Try changing your constraint to

CONSTRAINT  CheckDebute   CHECK (DebuteDate  Between 1950 and 2099),
CONSTRAINT  CheckDisband  CHECK (DisbandDate Between 1950 and 2099));

I had to insert the rows separately as sqlite doesn't support the same multi-row insert syntax but I did not get any duplication errors. You also might want to use varchar(100) instead of char(100). You'll save a lot of space.

[john_111]

Your insert for the band members ends in a , but it should end in a ;

There is no error in repeating the foreign key of bandname for each member, that is what foreign keys are for! That is not a duplicate entry. A duplicate entry is where you try to insert a row of date which has the same primary key as another row, even if the rest of the row is different.
eg ('Beyoncé Knowles', 'American', 'Vocals', 'No', 'Destinys Child'),
and then ('Beyoncé Knowles', 'American', 'Drummer', 'No', 'Guns and Roses')
and then ('Beyoncé Knowles', 'American', 'Bass Guitarist', 'No', 'The Stones')
as they all have the same primary key.

A joint primary key for your members table would get round the problem of Beyoncé Knowles moving to Guns and Roses as their new drummer.
ie MemberName and BandName is the correct primary key for members, because a person can be a member of several bands, and your current system doesn't allow for that possibility. And when she later joins The Stones as bass guitarist, it would still work.

[misstj555]

Thanks for the advice. Also, I never knew there was a memory difference when using varchar vs char and I forgot about joint key/column primary key. I will look over it again and fix that.

[Reverend Jim]

The length of all char fields is fixed when you create the table and therefore always known. A varchar field is, by definition, variable so the length of each field has to be stored for each row. The storage requirement to store the field length is usually a lot less (on average) than the wasted space you would have if you declared char(maxlen).