Saturday, May 12, 2012

Oracle DB: check constraints enumeration

Traditionally, if you want to restrict the values allowed for a column, you use a foreign key to a reference column in another table.
This can be quite boring, and lead to a proliferation of reference tables.

A simple alternative is using ENUM
CHECK (col1 IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday',
'Friday', 'Saturday', 'Sunday')

SQL developer supports this in the "check constraints", just create a constraint and type MYCOLUMNNAME IN ('Monday', 'Tuesday')

1 comment:

Unknown said...

Nice Article,
I had also written an Article about Disabling and Enabling Constraints, What precautions to be taken while performing these Operations etc.. in http://www.way2db.in/oracle-constraints.html