Menu
is there a way in SQL to create the constraint that a column has to be unique, if a specific column has a specific value?
Example: the rows are not really deleted, but marked as 'deleted' in the database. And within the 'not-deleted' rows, ValueA has to be unique:
ID ValueA ValueB Deleted
-----------------------------------------------------
1 'foo' 10 0
2 'bar' 20 0
3 'bar' 30 1
4 'bar' 40 1
5 'foo' 50 0 --NOT ALLOWED
I thought of something like a CHECK constraint, however I don't know how to do this.
612
To check for a unique constraint use the already provided method: select count(*) cnt from user_constraints uc where uc. table_name='YOUR_TABLE_NAME' and uc.
Expand the "General" tab. Make sure you have the column you want to make unique selected in the "columns" box. Change the "Type" box to "Unique Key". Click "Close".
The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint.
Yes, absolutely. A unique constraint creates a unique index.
with SQL92 this is not possible, may be you could implement something with a trigger
60
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
