want to enforce Foreign Key referring multiple Primary key(s) from various tables ? (More than one t

Posted by Parthibansk under Sql Server on 8/7/2012 | Points: 10 | Views : 831 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

want to enforce Foreign Key referring multiple Primary key(s) from various tables ? (More than one table)

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 8/7/2012 [Member] [MVP] Silver | Points: 25

0	Check It Out! 1. Primary key Table1: create Table Table1 ( Id Int Identity(1,1) Primary Key, Column1 varchar(10)) go 2. Primary key Table2: create Table Table2 ( Id Int Identity(1,1) Primary Key , Column2 varchar(10)) go 3. Child Table3: Create Table Table3 ( Id Int , Column3 varchar(10)) go 4. Enforcing Foreign key on "Table3" referring "Table1.ID" and Table2.ID" Alter Table Table3 Add Constraint FK_Id Foreign Key(Id) References Table1(Id) Go Alter Table Table3 Add Constraint FK_Id1 Foreign Key(Id) References Table2(Id) Go Ih these case, Value should be there in both tables Table1.Id and Table2.Id then only you can use that value in Table3.Id, So the FK constraint validates on both Referred table(s). Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Parthibansk, if this helps please login to Mark As Answer. \| Alert Moderator

Check It Out!

1. Primary key Table1:

create Table Table1

(

Id	Int Identity(1,1) Primary Key, Column1 varchar(10))

go

2. Primary key Table2:

create Table Table2

(

Id	Int Identity(1,1) Primary Key , Column2 varchar(10))

go

3. Child Table3:

Create Table Table3

(

Id	Int , Column3 varchar(10))

go

4. Enforcing Foreign key on "Table3" referring "Table1.ID" and Table2.ID"

Alter Table Table3 Add Constraint FK_Id Foreign Key(Id) References Table1(Id)

Go

Alter Table Table3 Add Constraint FK_Id1 Foreign Key(Id) References Table2(Id)

Go

Ih these case, Value should be there in both tables Table1.Id and Table2.Id then only you can use that value in Table3.Id, So the FK constraint validates on both Referred table(s).

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/4/2012 [Member] Bronze | Points: 25

0	CREATE TABLE FOREIGNTEST(F_ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),F_ORDER VARCHAR(20),ONUM INT,STUDENT_ID INT, CONSTRAINT F_ORDER FOREIGN KEY(ONUM) REFERENCES TABLE1(ONUM) ,CONSTRAINT F_CATEGORY FOREIGN KEY(STUDENT_ID) REFERENCES TABLE2(ST_ID)) Parthibansk, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts