SQL Server Composite Primary Keys

Question

I am attempting to replace all records for a give day in a certain table. The table has a composite primary key comprised of 7 fields. One such field is date.

I have deleted all records which have a date value of 2/8/2010. When I try to then insert records into the table for 2/8/2010, I get a primary key violation. The records I am attempting to insert are only for 2/8/2010.

Since date is a component of the PK, shouldn't there be no way to violate the constraint as long as the date I'm inserting is not already in the table?

Thanks in advance.

Answer 1

You could have duplicates in the data you are inserting.

Also, it is a very, very, very poor practice to have a primary key that consists of 7 fields. The proper way to handle this is to havea surrogate identity key and a unique index on the seven fields. Joining to child tables on 7 feilds is a guarantee of poor performance and updating records when they have child records becomes a nightmare and can completely lock up your system. A primary key should be unique adnit should NEVER change.

Answer 2

Do all the rows have only a date component in that field (i.e. the time is always set to midnight: 00:00:00)? If not, you'll need to delete the rows >= 2/8/2010 and < 2/9/2010.

Also, are you sure you're not accidentally trying to insert two records with the same date (and same values in the other 6 PK fields)?

Answer 3

Perhaps there's something going on here you're not aware of. When you insert a row and get a primary key violation, try doing a SELECT with the appropriate key values from the row which could not be inserted (after doing a ROLLBACK, of course) and see what you get. Or perhaps there's a trigger on the table into which you're inserting data that is inserting rows into another table which uses the same primary key but was not cleaned out.

You might try the following SELECT to see what turns up:

SELECT * FROM YOUR_TABLE WHERE DATE > 2/7/2010 AND DATE < 2/9/2010;

(Not sure about the proper format for a date constant in SQL Server as I haven't used it in a few years, but I'm sure you get the idea). See what you get.

Good luck.