primary key constraint ms sql server 7

[seosamh]

I have a table (NonMember) with a primary key of 'fullname'. When I try to insert a name (firstname & surname) that already exists in the table I am given the following message:
"Violation of PRIMARY KEY constraint 'PK_NonMember'. Cannot insert duplicate key in object 'NonMember'."

Some names are quite popular and I need to be able to insert several John Smiths or Mary Murphys, for example. The Fullname must remain as the primary key. [sig][/sig]

 

[karluk]

I'm afraid that your goal, as stated, is impossible. There is no way to have a primary key on a column that also must allow duplicates. That's what a primary key is - an identifier that's unique for every row in the table.

Probably the best you can do is to add a second column to your primary key, which would serve as a tiebreaker in case of duplicates. That would retain fullname in the key while eliminating the uniqueness issue.

I'm not familiar with your needs, but I would also tend to question why fullname must remain as the primary key, since it's really not suitable as a key. Perhaps you could get all you need by defining a nonunique index. [sig][/sig]