How to check for MultiColumn Unique before Adding Unique Constraint

[dlite922]

I'm writing two scripts to upgrade a database to make it more robust (basically adding lots of constraints). The first script is the "validator" that you run first to see if you can upgrade or not. The second script does the Alter Table commands.

i.e. I need a way to check uniqueness of two columns before adding the unique constraint on them.

I think this is common, but I'm not googling the right terms guess.

Any help greatly appreciated, guys!






Dan

[dlite922]

Here's how I solved it:

SELECT count FROM (SELECT COUNT(*) as count FROM mytable GROUP BY col1, col2, col3) WHERE count > 1;

This says give me a count of all rows that have all three columns the same (two or more rows with identical values for those three columns).

If there are duplicate (non-unique) rows, this count would be greater than 1, hence why I put it in a wrapper function and asked if there are any count greater than 1.

If there are any, this query should give me duplicates, else return empty if there are none.





Dan