Solved

the columns in table do not match an existing primary key or unique contraint

Posted on 2011-02-28
16
2,604 Views
Last Modified: 2012-05-11
I have a sql table that I am trying to setup a foreign key to another table.  This table does not have to have data in it, but if it does, it has 3 primary keys, and one of those primary keys is going to come from the ShipmentImportHeader table.  

When I am trying to do the following:
ShipmentImportPackage ForeignKey = ShipmentImportHeaderID
This points to the ShipmentImportHeader table and the column ID.

When I try to do this, I keep getting the message:
The columns in table 'ShipmentImportHeader' do not match an existing primary key or unique contraint.  Now, ShipmentImportHeader.ID is a primary key.  Also, in the ShipmentImportPackage table, the primary key is ID, ShipmentImportHeaderID, PackageID.  Now, I want the shipmentimportheaderid to be a foreign key to the ID field in the ShipmentImportHeader table.

I am not the best at sql, so this error does not mean too much to me.  Any help would be appreciated.
 ShipmentImportPackage table Foreign Key I am trying to setup ShipmentImportHeader table
0
Comment
Question by:Bran-Damage
[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
  • 6
  • 5
  • 5
16 Comments
 
LVL 15

Expert Comment

by:derekkromm
ID: 34997456
ShipmentImportHeader.ID isn't the PK, its only part of the PK

If the ID alone does not uniquely identify the records in that table, then you can't setup the FK in this manner.

You'd have to have all the columns that makeup the PK on the FK table (ShipmentImportPackage) as well and create the FK on all of those columns.
0
 

Author Comment

by:Bran-Damage
ID: 34997647
What is odd about that is I have another table called ShipmentImportLineItem and here is was able to have the FK set ShipmentImportLineItem.ShipmentImportHeaderID to the ShipmentImportHeader.ID field.

How would this be different than what I am trying to do on the package table? ShipmentImportLineItem Table
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34997675
That is because the ShipmentImportLineItem.ID column is the PK of that table (and is the only column that makes up the PK), whereas in the ShipmentImportPackage table, there are 3 columns that makeup the PK and, therefore, a FK would require a link on all 3 in order to create the required relationship.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34997689
why don't you try to make it like this

ALTER TABLE ShipmentImportPackage ADD CONSTRAINT FK_ShipmentImportPackage_ShipmentImportHeader FOREING KEY ShipmentImportHeaderID REFERENCES ShipmentImportHeader (ID)

Review table and column names, but that should work.

Cheers
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34997722
Sorry, mistypes


ALTER TABLE ShipmentImportPackage 
	ADD CONSTRAINT FK_ShipmentImportPackage_ShipmentImportHeader 
	FOREIGN KEY ShipmentImportHeaderID 
	REFERENCES ShipmentImportHeader (ID)

Open in new window

0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34997743
raul, that doesn't work, ShipmentImportHeader.ID isn't a unique key or PK
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34997817
Ouch!! ...

So toolKitID and deliveryDocNumber are primary keys of the table??...

That's not really nice, ID is enough for a PK, ....

In case you cannot do anything about that, you should create those fields as well in ShipmentImportPackage to be able to create a FK... (3 parent fields - 3 child fields.) .

But you really do need to review the PK of your tables

http://www.google.es/search?sourceid=chrome&ie=UTF-8&q=HOW+TO+CHOOSE+A+PRIMARY+KEY

good luck

0
 

Author Comment

by:Bran-Damage
ID: 34997915
No, ShipmentImportHeader.ID is in the PK, please see screenshot44 in the first question.  

The reason I have three columns here as the key is because I could have a toolkit 1 and tool kit 2 which both have the same delivery doc number
0
 

Author Comment

by:Bran-Damage
ID: 34997921
but, I need them to be unique within the combination of toolkit and delivery doc number.

0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34997972
If you want to setup the FK, then you'll need to add the ToolKit and DeliveryDoc columns to the other table.

If that doesn't make sense logically, then you can't setup a FK between the 2 tables.
0
 
LVL 8

Expert Comment

by:raulggonzalez
ID: 34997982
Sorry Bran-Damage,

please try what I posted and let me know...

derekkromm, I think you're wrong... (according at what Bran-Damage just posted.)


cheers
0
 

Author Comment

by:Bran-Damage
ID: 34998135
I ran the above statement, and am getting this error on this part FOREIGN KEY ShipmentImportHeaderID

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'ShipmentImportHeaderID'
0
 

Author Comment

by:Bran-Damage
ID: 34998219
Just so we are clear here, what I was hoping to do is make the primary key on the header table the ID (which is an identity), DeliveryDocNumber (Which is user entry), ToolKit(Which is user entry)  hmm, now that I am typing this out, ID will alwasy be unique since it is an identity type, the other two could be the same.

Example of Ok -
ID = 1
DeliveryDocNumebr = 5
Toolkit = 1

id = 2
deliverydocnumber = 5
toolkit = 2

Example of not ok:
ID = 1
DeliveryDocNumebr = 5
Toolkit = 1

ID = 2
DeliveryDocNumebr = 5
Toolkit = 1

Now, since I have the ID in the primary key, it will allow the user to insert this data because the ID is different, but, i do not want the user to be able to do this.

Then, with the other tables, I want the ID of that table to be the reference from all other tables back to the main record that has header data in it.

It seems like I might not be able to do this.  The way that I am trying to do it.
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 250 total points
ID: 34998231
you can make the ID the PK
then make the toolkit + deliverydoc a unique index

that will prevent duplicates of those 2 combos being inserted

then you can create the FK based on the ID column as you were trying to before
0
 
LVL 8

Assisted Solution

by:raulggonzalez
raulggonzalez earned 250 total points
ID: 34999402
Sorry, just some parenthesis were missing...


Try again ;-)


Cheers

ALTER TABLE ShipmentImportPackage 
	ADD CONSTRAINT FK_ShipmentImportPackage_ShipmentImportHeader 
	FOREIGN KEY (ShipmentImportHeaderID)
	REFERENCES ShipmentImportHeader (ID)

Open in new window

0
 

Author Closing Comment

by:Bran-Damage
ID: 35007489
Thanks for your help guys.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 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