May 4, 2013 at 4:14 am
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/
May 4, 2013 at 5:16 am
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)
May 4, 2013 at 5:32 am
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/
May 4, 2013 at 9:33 pm
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.
May 6, 2013 at 9:50 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply