Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 18-02-2009
Member
 
Join Date: Jan 2009
Posts: 74
Multiple Foreign Key in T-SQL Syntax

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.
Reply With Quote
  #2  
Old 18-02-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: Multiple Foreign Key in T-SQL Syntax

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.
Reply With Quote
  #3  
Old 18-02-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: Multiple Foreign Key in T-SQL Syntax

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.
Reply With Quote
  #4  
Old 18-02-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: Multiple Foreign Key in T-SQL Syntax

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) )
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Multiple Foreign Key in T-SQL Syntax"
Thread Thread Starter Forum Replies Last Post
Foreign Direct Investment RastogiJI Off Topic Chat 9 26-02-2011 02:43 PM
Foreign key relationship among tables in SQL Satchel Software Development 4 06-01-2011 07:21 AM
Primary key and foreign key differentiation Smita.bendal Software Development 3 05-12-2009 08:55 AM
Need to replace foreign characters in php hamX15 Software Development 2 20-06-2009 11:51 AM
When to use foreign keys ? Balgovind Software Development 4 21-04-2009 12:11 PM


All times are GMT +5.5. The time now is 01:03 PM.