SQL Columns and Unique Values Question

[zachattack05]

Still working to figure this stuff out :)

My question is, is it possible to set a combination of columns with a unique constraint?

For example:

ColumnA | ColumnB
-----------------
Value1  | Value2     <- OK
Value1  | Value1     <- OK
Value2  | Value1     <- OK
Value2  | Value2     <- OK
Value1  | Value2     <- Not OK, combo already exists in the table

Any ideas? Or is this something that needs to be enforced in the logic layer?

[jfarrugia]

You can select column A + column B as composite keys in the database.. meaning that both columns together are unique values. DB will trigger off a p.k error if you attempt to insert duplicate keys (since a + b in composite form a p.k)

[zachattack05]

Are there any performance considerations when using these keys in combination with FKs and cascading updates/deletes?

[jfarrugia]

as far as i know, it shouldn't make much of a difference. It is a common misconception that p.ks must be unique values within one column.. whereas that is simply an identity column.. p.k.s ensure that each RECORD within your table is unique. Hence if what you need is having two columns whose values can not be duplicated, you must use a composite key.

[zachattack05]

Wonderful!

Thanks!