On an Oracle DB, if I have a Nullable column and a check constraint on the same column restricting the values to, say, 'ABC' or 'DEF', can I insert a row with a null value in that column (given that null is not one of the constraint values)?
Asked
Active
Viewed 5,800 times
2
-
Yes, Oracle will enforce all restrictions that are enabled. Can you share the constraint code so the question can be answered? – Álvaro González Jan 04 '16 at 11:30
-
1why do you ask? Try it out! http://sqlfiddle.com – miracle173 Jan 04 '16 at 12:00
2 Answers
9
You can read up on check (and other) constraints in the Oracle Concepts manual.
In your case, yes you can insert a row with a null value because the check constraint (e.g. mycolumn in ('ABC', 'DEF') becomes effectively NULL in ('ABC', 'DEF') when mycolumn is null. This Boolean expression will itself evaluate to null, just as null = 'ABC' does, because null is "unknown". And check constraints are only violated if they evaluate to false. Hence your constraint allows you to insert 'ABC', 'DEF' or null into the column.
Tony Andrews
- 129,880
- 21
- 220
- 259
2
can I insert a row with a null value in that column
Yes you can since you have defined your column as nullable. If you supply a non-null value to the column then the supplied value must match with the values defined in CHECK constraint.
Rahul
- 76,197
- 13
- 71
- 125