Solved

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

Posted on 2011-02-28
16
2,408 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
  • 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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

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.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

705 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

18 Experts available now in Live!

Get 1:1 Help Now