Creating FK for multiple columns

Question

Creating FK for multiple columns

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
March 31, 2017 at 10:13 am

#329010

I have 2 tables
dbo.Employee
Empl Id1
Emp id2
EMp id3
All these there columns ar Primary key in employee tble
I have created UNIQUE NONCLUSTERED on these columns on dbo.Emp table
LTER TABLE [dbo].[Emp] ADD CONSTRAINT [IX_EMP] UNIQUE NONCLUSTERED
(
[Emplid1] ASC,
[EMplid2] ASC,
[Emplid3] ASC
dbo.Student
Empid1
Empid2
Emplid3
In this table I want to make these columns as FK.
How to make it?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

I think knowing what you are trying to accomplish would be a good first step.

That being said, you can make a FK point to any PK. So there is no reason that you can't just make a FK for each of the 3 columns in the student table, but I have a feeling this might not accomplish what you are trying to do.

A FK basically means that the column must have a corresponding value in the PK it is pointing to. I don't think you can have a FK pointing to a constraint. So you would have Empid1 pointing to dbo.Employee.Id1 (for example), correct? and 2 to 2 and 3 to 3. Is this what you are wanting?
for example if you had these values in employee:
Id1 Id2 Id3
1 1 1
2 3 4
3 2 3

you could have these values in student without any issues:
emplId1 emplId2 emplId3
1 2 3
1 1 1
2 1 4
3 3 3
etc

your unique constraint is just saying you can't have 2 rows that have the same value for those 3 columns.

I believe this is the case anyways. I could be mistaken (it has happened before).

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 2

actually SQL Server does let you add a foreign key to a unique constraint such as this, you just have to reference all the columns in both sides of the constraint, like this:

ALTER TABLE dbo.Student ADD CONSTRAINT [FK_Student_Employee] FOREIGN KEY (Emplid1, Emplid2, Emplid3) REFERENCES dbo.Employee (Emplid1, Emplid2, Emplid3);