Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-28
16
Medium Priority
?
2,817 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1000 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 1000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

885 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