Solved

Foreign Key Vs Non-Primary key

Posted on 2013-02-07
5
438 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now