May 12, 2008 at 9:49 pm
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
May 12, 2008 at 11:27 pm
Hi
Tell me why do you want to create foreign key like this....
Please check this link...
http://www.dbforums.com/archive/index.php/t-1097776.html
http://msdn.microsoft.com/en-us/library/ms175464.aspx
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
Thanks
jaypee.s
May 14, 2008 at 7:21 am
Yes, it is possible, but it's strange.
May 14, 2008 at 8:02 am
This would mean the column's value must exist in both "parent" tables ?
IMO that doesn't feel like it should.
A key is just a key and should represent nothing else but that.
use SSC_Test
go
create table P1 (idnr int identity(1,1) not null primary key clustered, colx varchar(128) not null);
create table P2 (idnr int identity(1,1) not null primary key clustered, coly varchar(128) not null);
create table C1 (idnr int identity(1,1) not null primary key clustered, thefkcol int not null, colZ varchar(128) not null);
go
ALTER TABLE dbo.C1 ADD CONSTRAINT
FK_C1_P1 FOREIGN KEY
(
thefkcol
) REFERENCES dbo.P1
(
idnr
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.C1 ADD CONSTRAINT
FK_C1_P2 FOREIGN KEY
(
thefkcol
) REFERENCES dbo.P2
(
idnr
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
set nocount on;
insert into P1 (colx) values ('ParentA');
insert into P1 (colx) values ('ParentB');
insert into P1 (colx) values ('ParentC');
insert into P1 (colx) values ('ParentD');
Delete from P1 where colx='ParentC';
insert into P2 (coly) values ('ParentX');
insert into P2 (coly) values ('ParentY');
insert into P2 (coly) values ('ParentZ');
insert into P2 (coly) values ('ParentW');
set nocount off;
insert into C1 (thefkcol, colZ) values (1,'ParentA and ParentX');
insert into C1 (thefkcol, colZ) values (2,'ParentB and ParentY');
go
/* must go wrong */
insert into C1 (thefkcol, colZ) values (3,'ParentC and ParentZ');
go
insert into C1 (thefkcol, colZ) values (4,'ParentD and ParentW');
go
select *
from C1
order by idnr
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply