November 27, 2002 at 5:22 am
Hello all, Some of the tables in my database seem to have the same foreign key constraints more than once. I had to drop the constraints on some of my tables, when I did this I didn't drop all of them but when I ran a script to add them back in I accidentally added the same one more than once. The constraint name is slightly difference(has a different combination of numbers at the end). Is it safe to just delete the duplicates, if so which one. If I don't delete the duplicates is this going to cause a problem and how come SQL Server does not error when attempting to add the same foreign key reference more than once? Thanks to anyone who can provide any info on this.
November 27, 2002 at 5:27 am
Hi, i think the best solution would be just drop all and create again. In the script you can give a constraint name so that you easier can delete it and you know that there is only one.
November 27, 2002 at 5:43 am
quote:
Hi, i think the best solution would be just drop all and create again. In the script you can give a constraint name so that you easier can delete it and you know that there is only one.
Hi, do you know if there is a reason why SQL Server doesn't give an error or warning when attempting to add duplicates?
Thanks
November 27, 2002 at 7:30 am
hi, no i don't know exactly, i could not finding a restriction in bol. I think the only check is the unique name. The same applies to indexes, you could create duplicate indexes on the same columns.
November 27, 2002 at 8:31 am
Yes FKs do not verify if the same columns exist in a different FK. Technically this doesn't break it so it does not cause an issue, just a pain to find if you make this mistake. However, I suggest putting in a Wish request to MS on this as it would be nice to be sure only one FK constriant for the same fields between tables exists.
December 1, 2002 at 7:57 pm
Funny, I passed along and heard back from them. No promises in 2KSP3 (even thou that is the channel I passed on to) but they did state they would look into it.
December 5, 2002 at 7:59 pm
In many ways, SQL Server is kinda stupid. Or is it that it just expects you to know what you're doing? Seems to me another index that duplicates an existing one or something like you point out would be worth at least a warning message.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply