Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple reference to a single column Expand / Collapse
Author
Message
Posted Saturday, May 4, 2013 4:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
I have created 3 table in which in T3 table I am referencing the ID columns of table T1 and T2.
I have created two separate Foreign Key constraints on ID3 column but its not working.
Please find the info below:
create table t1
(
ID int primary key identity(1,1),
Name varchar(100)
)
Create table T2
(
ID2 int primary key identity(1,1),
Name2 varchar(100)
)

Insert into T1 values('A'),('B'),('E'),('G')
INERT INTO T2 values ('L'),('K'),('M')

Create table t3
(
ID3 int,
Name3 varchar(100)
)

alter table t3
ADD constraint FK_ID4 foreign key (ID3) references t1(ID)


alter table t3
ADD constraint FK_ID3 foreign key (ID3) references t2(ID2)

Insert into t3 values (4,'G')

Error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ID3". The conflict occurred in database "dbTester", table "dbo.T2", column 'ID2'.



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1449404
Posted Saturday, May 4, 2013 5:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
Because Id value 4 is not exits in t2 table. Thats why you getting error.

If you have foreign key then the reference column value must exists in parent table.

below code will work.

create table t1
(
ID int primary key identity(1,1),
Name varchar(100)
)
Create table T2
(
ID2 int primary key identity(1,1),
Name2 varchar(100)
)

Insert into T1 values('A'),('B'),('E'),('G')
INsERT INTO T2 values ('L'),('K'),('M'),('S')

Create table t3
(
ID3 int,
Name3 varchar(100)
)

alter table t3
ADD constraint FK_ID4 foreign key (ID3) references t1(ID)


alter table t3
ADD constraint FK_ID3 foreign key (ID3) references t2(ID2)

Insert into t3 values (4,'G')

drop table t3
drop table t2
drop table t1


Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #1449408
Posted Saturday, May 4, 2013 5:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
malleswarareddy_m (5/4/2013)
Because Id value 4 is not exits in t2 table. Thats why you getting error.

If you have foreign key then the reference column value must exists in parent table.

below code will work.

create table t1
(
ID int primary key identity(1,1),
Name varchar(100)
)
Create table T2
(
ID2 int primary key identity(1,1),
Name2 varchar(100)
)

Insert into T1 values('A'),('B'),('E'),('G')
INsERT INTO T2 values ('L'),('K'),('M'),('S')

Create table t3
(
ID3 int,
Name3 varchar(100)
)

alter table t3
ADD constraint FK_ID4 foreign key (ID3) references t1(ID)


alter table t3
ADD constraint FK_ID3 foreign key (ID3) references t2(ID2)

Insert into t3 values (4,'G')

drop table t3
drop table t2
drop table t1

But this will not fulfill my scenario that I required. I want column ID of table T3 should refer the ID col of T1 and T2 as it contain values from both table.



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1449409
Posted Saturday, May 4, 2013 9:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
A foreign key constraint requires that the value exist in the foreign table, so adding two foreign key constraints means that the value you add to T3 must exist in both T1 and T2. There is no way to use foreign key constraints to require that the value exist in either T1 or T2.

Although it is more complex than a constraint, I think the only way you will achieve the either/or solution you are looking for is with a DML trigger. In a trigger you can use subqueries to check both tables for the value to be added to T3 and only allow the insert to complete if a match is found in either one. I'm not familiar enough with the trigger syntax to give you a complete statement, but I do believe you need to look in the direction of triggers to find the solution to your problem.
Post #1449488
Posted Monday, May 6, 2013 9:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:05 AM
Points: 1,222, Visits: 2,545
You can do this with a CHECK constraint that calls a scalar function, although I suspect that this could have a significant impact on performance, especially when inserting a lot of rows in one batch. Create a scalar function with a CASE expression that sets the return value to 1 if the ID3 exists in either T1 or T2 and 0 if not. Add the CHECK constraint to T3: CHECK (dbo.scalarFunction(@ID3) = 1).

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1449766
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse