August 19, 2011 at 12:00 am
Hi All,
I have a table
Create table tableA
(
id int,
num int,
desc varchar(10)
primary key (id,num)
)
create table tableB
(
id int identity primary key,
num int,
name varchar(10),
address varchar(10)
)
i want to set a foreign key relation with tableA to TableB
Please help me out
Thanks in Advance
August 19, 2011 at 12:52 am
First question: What are you trying to accomplish using the id+num column combination ?
( Why not only the id column as pk and an uk on num+id ?)
A foreign key constraint can only ref a full primary key / unique key.
You need to determine the direction of the relationship (tableA parent or tableB for parent ?)
e.g.
ALTER TABLE dbo.tableA ADD CONSTRAINT
FK_tableA_tableB FOREIGN KEY
(
id,
num
) REFERENCES dbo.tableB
(
id,
num
) ON UPDATE NO ACTION
ON DELETE NO ACTION
;
ALTER TABLE dbo.tableB ADD CONSTRAINT
FK_tableB_tableA FOREIGN KEY
(
id,
num
) REFERENCES dbo.tableA
(
id,
num
) ON UPDATE NO ACTION
ON DELETE NO ACTION
;
BTW: By default you should add an FK-index to your child table that matches the PK/UK it references to (columns, in order) to support the relationship, unless it is proven this extra index hurts your data system.
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
August 19, 2011 at 7:02 am
Hold a second. TableB has an identity column. You can't make that a foreign key to another table, it will cause errors because, in theory, you're creating values automatically in that table. I'd expect to see the identity in TableA, unless, TableB is meant to be the parent & TableA is the child...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 19, 2011 at 7:11 am
In TableA i am having composite Primary key for the columns (id,num)
In TableB i have to set a foreign key for the column 'num'
I was given like this:-
create table tableB
(
id int primary key,
num int foreign key references tableA(ID,num)
)
With this syntax it is showing Errors.. Can you please help me out in this
how can i create a foreign key with composite primary key...
Regards,
SqlSpider:-)
August 19, 2011 at 7:46 am
SqlSpider... (8/19/2011)
In TableA i am having composite Primary key for the columns (id,num)In TableB i have to set a foreign key for the column 'num'
I was given like this:-
create table tableB
(
id int primary key,
num int foreign key references tableA(ID,num)
)
With this syntax it is showing Errors.. Can you please help me out in this
how can i create a foreign key with composite primary key...
Regards,
SqlSpider:-)
so the identity property for id was a typo ?
As I posted in my previous reply .... Child TableB to Parent TableA
ALTER TABLE dbo.tableB ADD CONSTRAINT
FK_tableB_tableA FOREIGN KEY
(
id,
num
) REFERENCES dbo.tableA
(
id,
num
) ON UPDATE NO ACTION
ON DELETE NO ACTION
;
Hence my question : Why this denormalization ?
- Personally I would prefer a single column foreign key
- What's the added value of the id column to the num column ? If it is a sort of virsioning, maybe better to generate an added value by adding a datetime column and make that the unique constraint for num+datetime and put the PK on the ID coliumn to be used for relationships. (in my exp. many questions over time come to when did this parameter value become active, hence my datetime indication preference)
Did you apply Codd's rules to your design ?
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
August 19, 2011 at 12:31 pm
Here I am having two Databases... I am going to migrating those two databases into single databases..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply