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 : 810 | Status : [Member] | Replies : 2
want to enforce Foreign Key referring multiple Primary key(s) from various tables ? (More than one table)




Responses

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

Up
0
Down
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

Up
0
Down
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

Login to post response