Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Foreign Key Vs Non-Primary key

Posted on 2013-02-07
5
Medium Priority
?
453 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 1000 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 1000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

618 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