4

Today I found an interesting constraint on one of the tables:

alter table T1 add constraint C$T1_DUMMY check (null is null);

Any idea why we need this?

andy
  • 5,979
  • 2
  • 27
  • 49
turbanoff
  • 2,439
  • 6
  • 42
  • 99
  • I can't think of any good reason to have that constraint. Is your DDL version controlled? Ideally the script that created that constraint would have a comment. Or you could look in the repository history and see who made the change, when, and why (if they left a useful comment). – Jon Heller Feb 07 '13 at 19:15
  • 1
    the command may have been auto-generated from some custom data dictionary using a query with meagre validations. or - as its name would suggest - the constraint is supposed to be a template of some kind. – collapsar Feb 08 '13 at 13:29
  • 3
    It's a reality check. When it fails, we know the world has ended. Or there's a fundamental bug in Oracle. Which amount to pretty much the same thing :) – Jeffrey Kemp Feb 12 '13 at 03:05

2 Answers2

0

The answer is very simple.

The condition null is null will always return true. Hence the CHECK statement always returns true and therefore doesn't enforce any additional business logic. Most probably this is why the constraint is named as C$T1_DUMMY.

I also tried it on my own machine, on the emp table that comes in Oracle's own sample schema. Works perfectly. Nothing is barred except from syntax errors and other constraints' enforcements.

Rachcha
  • 8,486
  • 8
  • 48
  • 70
  • 1
    Correct. I would add only that a constraint that's always true is never false, and a constraint that's never false is not -- in the logical sense -- a constraint. It's logically void, and you cannot need it. Just remove it. – James K. Lowden Feb 15 '13 at 23:24
  • Yes, James is right. and just as the name of the constraint `C$T1_DUMMY` - it's just a dummy constraint. It was perhaps created to check whether the user had the permission to add constraints to table. You can remove it if you don't need it or you are uncomfortable seeing it there. – Rachcha Feb 18 '13 at 04:38
0

Possible cases:

One of the meaningful use case would be to check if the running user has privileges to create a constraint on the table.

Exists because it's a tool/script-generated code.

To show more lines of code in his script, of course at the cost of overhead on the table.

sankar
  • 189
  • 1
  • 10