• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

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)
0
PtboGiser
Asked:
PtboGiser
  • 2
  • 2
2 Solutions
 
virtuadeptCommented:
A foreign key constraint can be on a non primary key column or group of columns. It can even have nullable columns in it but if any of the columns are null for a row then the rest of the columns are not checked.
0
 
tim_csCommented:
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.
0
 
PtboGiserAuthor Commented:
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!
0
 
tim_csCommented:
It looks like you're missing values in your County Road Section table.

Run this and see if you get any results.

SELECT
   *
FROM
   ASupport_Sign a
   LEFT JOIN County_Road_Section c
      ON a.RoadSecID = c.RoadSecID
WHERE
   c.RoadSecID IS NULL.
0
 
PtboGiserAuthor Commented:
Thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now