views:

73

answers:

3

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.

+2  A: 

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.

HLGEM
Thanks. I'm aware of the flaws in the table design, but unfortunately it's not up to me to change, just to make it work.
Colin
Also, you were right! I must have double loaded records into the other table, which didn't have a PK. Thanks again.
Colin
LOL, how do you think I knew the answer so fast, if I hadn't done it myself a time or two.
HLGEM
Slightly off-topic, but where did this idea come from that a primary key should "never change"? It's often desirable to have keys that don't change but there is no fundamental reason why a key should not / must not change any more than any other attributes. Sometimes it's useful to change key values.
dportas
+2  A: 

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)?

Daniel Renshaw
A: 

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.

Bob Jarvis