Multiple Foreign Key in T-SQL Syntax

[Amaresh]

Hi there.
I am not a sql programmer; so forgive me if I am somewhere going wrong. My question is that in a SQL Server database, Is it possible to create multiple foreign keys on a particular table and every column in the table should have some null values? And if the table already consists of some primary key and you are in need for creating foreign keys, is it that you have to make the new column for the primary key in order for creating a foreign key?

Please provide examples or illustrations on how to create multiple foreign keys on tables as a unique constraint while some of the values in these new foreign keys will have some null values?

Please help me in this query.

[Praetor]

First error means there are duplicate records in your operating systems system column of applications table. Naturally, you can't put a unique constraint on it.

Second error is because of the absence of the unique constraint that we tried to create in first statement and fails.

If you can't remove duplicate values from operating systems systems column of applications table, you can't do this activity.

[Katty]

You are safer dropping and recreating them for the new table.

Or, can you re-use the same Primary Keys for the new table that were in the Old table you are replacing? Then you don't have to update the FKs at all.

If the Primary Key is auto-incrementing "Identity" column, you can use "Set Identity_Insert On", to allow you to insert new records into the new table's identity column with an explicit value (the pk from the old table). Run Set Identity_Insert Off when you're done and you're off and running.

[kelfro]

You cant define two foreign keys on same column to link to two tables. I think as a workaround what you can do is to create two columns halllocation & flatlocation instead of location. then link halllocation to 'hall' (column ID:'locNo') via a foreign key and also link flatlocation to 'student_flat' (column ID:'flatNo') by means of another foreign key. This ensures both the columns will have only allowed values from master table. Now to make sure you've either one of columns having a value in each record of bedroom create a check constraint to ensure either of them is not null. so it will be like


CONSTRAINT FK_bedroomFlatLoc FOREIGN KEY (flatlocation)
REFERENCES student_flat(flatNo)
, CONSTRAINT FK_bedroomHallLoc FOREIGN KEY (halllocation)
REFERENCES hall(locNo)
CONSTRAINT CK_bedroomLocationHallOrFlat
CHECK ( (flatlocation IS NULL AND halllocation IS NOT NULL)
OR (flatlocation IS NOT NULL AND halllocation IS NULL) )