Solved

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

Posted on 2011-02-28
16
2,497 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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