CODEWITHSUNDEEP
sql-server-2005foreign-keysconstraints
Dindar
Dindar

Reputation: 3235

constraint Problem /Solution in sqlServer

Sorry for my shallow question .
Imagine I have 4 Tables in my DataBase (SQLServer 2005).
Picture , News , product and gallery Tables . At my Picture Table i Have 3 foreign key to all 3 other tables , And all foreign keys are nullable and default value = -1 . All tables have Primary keys and is Identity .

At my webform every photo that i insert is related to one , two or three other tables .
example : it could be related to NewsId =4 , galleryId= 2 and not related to Product Table . So ProductId = -1.
I don't know how to insert photo and not give this error "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Picture_Product" "
. I know why is that , and that's because of inforce FOREIGN KEY constraint . But i don't know how to design my DataBase in order overcome this problem .I hope i could come it across .

p.s:In addition in future i want query my Picture table according News , Product or Gallery or mixed of them .

Upvotes: 1

Views: 108

Answers (3)

dugbugs
dugbugs

Reputation: 36

Why do you have a default value of -1 for your foreign keys? When you insert a new row that doesn't populate one of the foreign keys, then the foreign key will be set to -1 which doesn't match the keyof a row in the foreign table.

I normally just have no default for the foreign key and if there is no link to a row in the foreign table then the foreign key is null. This is legal because you have defined all the foreign keys as nullable

Upvotes: 1

KM.
KM.

Reputation: 103587

why use -1 for undefined? that is what NULL is for! delete the default and allow those rows to have NULL when there is no FK row.

Upvotes: 1

remi bourgarel
remi bourgarel

Reputation: 9389

it's because you don't have any product with the ID -1, you have to set the default value at NULL and insert null value instead of -1

When you create a foreign key, the sql server check that the inserted foreign key exists in the referenced table, in you case it doesn't exists so the RDMS refuses to insert the row.

Upvotes: 1

Related Questions