asked on

Foreign Key Vs Non-Primary key

Is it possible to create a foreign key in one column and connect it to a non primary key in another column?

For example I want Stop_Sign (RoadSecID) as the foreign key to ASuppot_Sign (RoadSecID) which is not a primary key in the table.

ALTER TABLE Stop_Sign
ADD CONSTRAINT fk_Stop_Sign
FOREIGN KEY (RoadSecID)
REFERENCES ASuppot_Sign(RoadSecID)

ASKER CERTIFIED SOLUTION

virtuadept

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tim_cs

You can do that but you have to add a unique constraint to the RoadSecID column in ASupport_Sign

http://msdn.microsoft.com/en-us/library/ms175464(v=sql.105).aspx

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.

PtboGiser

ASKER

Thanks for your responses they were helpful!

I have been successful in creating the unique constraint on my County Road Section table and I can see that its populated; however, I still cannot create the foreign key on my ASupport_Sign table.

For example the unique constraint was created on RoadSecID of County_Road_Section and now I would like to create a foreign key on RoadSecID of ASupport_Sign. I would like to eventually create a relationship between RoadSecID of County_Road_Section (Parent table) and RoadSecID of ASupport_Sign (child table).

Code:

ALTER TABLE ASupport_Sign
ADD CONSTRAINT fk_ASupport_CNTYRDSec
FOREIGN KEY (RoadSecID)
REFERENCES County_Road_Section(RoadSecID)

Error Message:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_ASupport_CNTYRDSec". The conflict occurred in database "CNTY_GIS", table "dbo.County_Road_Section", column 'RoadSecID'.

Thanks!

SOLUTION