question

Slick84 avatar image
Slick84 asked

Define UNIQUE Constraint on Composite Key

How would I be able to define a Unique constraint on a composite PK which consists of 6 different columns? I am having some trouble inserting data into this table because of a PK constraint. Since it's 6 columns, I'm not sure how to group by and count the line items which are duplicated. I read somewhere that instead of me doing it, I can have the DB take care of this automatically by putting a unique constraint on this composite key and then it'll insert based on that. Im a little confused but thought I'd give it a go since I thought my composite key I defined was exactly for uniqueness.
primary-keykeycompositeunique-constraint
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
If there is already a primary key on the six columns, then you're done. A primary key is a unique constraint. I'm not sure why you need to count duplicate items because you shouldn't have any if it's a PK. What error are you getting when you try to add the constraint?
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OK. Then all you have to do is select from the table where the six columns equal the six values you're attempting to add. If there's already data in there that's identical to what you're adding, then the constraint is working as advertised.
6 Likes 6 ·
My composite PK is already set. The error comes when I insert data. I have done a distinct on the data and everything, yet still when I try to insert, it errors out saying I've violated the PK constraint.
0 Likes 0 ·
Slick84 avatar image
Slick84 answered
The PK violation was coming up because my first column out of the 6 columns that make up the PK was set to a data type of DATE. I was trying to insert a DATETIME value meaning 1/1/2011 12:12:12 into this which would come back with a PK violation. Overlooked and learned that the explicit conversion from datetime to date didn't work by default. Thought i'd post an update for any interested parties. Thanks, Slick
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.