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.
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?
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.
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
