Re: Q: Not Null constraints

In article <1994Aug4.120010.15509_at_ericsson.se>, ebcrbj_at_ebcw150.ericsson.se (BO/EBC/KX/RGFC Roger Bjaerevall 08-682 4851) writes:
|> NOT NULL constraints can be named in the table definion.
|>
|> Is it possible to get the constraint name upon a request that
|> inserts a NULL value into the column?
|>
|> Even if the constraint is named I'll get this message:
|>
|> ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
|>
|> Quite hard to find out which column that failed ;-)
|>
|> +------------------------------------------------------------------+
|> + _/_/_/ _/_/_/ _/_/_/_/ Roger Bjärevall +
|> + _/ _/ _/ _/ _/ BO/EBC/FL/RGFC +
|> + _/ _/ _/ _/ _/ Ericsson Business Networks AB +
|> + _/_/_/_/ _/_/_/_/ _/ 135 83 Tyresö +
|> + _/ _/ _/ _/ _/ _/ Sweden +
|> + _/ _/ _/ _/ _/ _/ +46 8 6824851 +
|> + _/ _/ _/_/_/ _/_/ email: ebcrbj_at_ebc.ericsson.se +
|> +------------------------------------------------------------------+
|>

Try creating the table in this manner:

SQL> create table ttt (f1 number constraint f1_not_null check (f1 is not null));

Then when we insert null we get:

SQL> insert into ttt values (NULL);
insert into ttt values (NULL)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.F1_NOT_NULL) violated

I don't know whether or not this is more efficient, but it will give you a different error with the named constraint. You could also enforce this with a before insert or update for each row trigger, which would check each column of the :new record for null values, and raise_application_error() with a meaningful message listing which columns were null. This method would have the advantage also of being able to tell you that more than one required column is null in one error message. Received on Thu Aug 04 1994 - 18:48:53 CEST