Solved

Foreign Key Vs Non-Primary key

Posted on 2013-02-07
5
445 Views
Last Modified: 2013-02-07
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
Comment
Question by:PtboGiser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
virtuadept earned 250 total points
ID: 38864214
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
 
LVL 15

Expert Comment

by:tim_cs
ID: 38864215
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
 

Author Comment

by:PtboGiser
ID: 38864713
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
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 250 total points
ID: 38864741
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
 

Author Closing Comment

by:PtboGiser
ID: 38865169
Thx
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question