Menu
Using Oracle, if a column value can be 'YES' or 'NO' is it possible to constrain a table so that only one row can have a 'YES' value?
I would rather redesign the table structure but this is not possible.
[UDPATE] Sadly, null values are not allowed in this table.
879
Column Level Constraint is used to apply a constraint on a single column. Table Level Constraint: Table Level Constraint is used to apply a constraint on multiple columns.
CHECK constraint is used to restrict the value of a column between a range.
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column.
Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow. a table-level constraint. Table-level constraints refer to one or more columns in the table.
Use a function-based index:
create unique index only_one_yes on mytable
(case when col='YES' then 'YES' end);
Oracle only indexes keys that are not completely null, and the CASE expression here ensures that all the 'NO' values are changed to nulls and so not indexed.
83
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
