Re: Not Null constraints
Date: 1996/12/08
Message-ID: <58drjj$nnu_at_mtinsc01-mgt.ops.worldnet.att.net>#1/1
ORACLE enforces NOT NULL constraints as CHECK constraints (i.e., as domain integrity constraints, not entity integrity constraints); therefore, the CONSTRAINT_TYPE column in *_CONSTRAINTS tables (*=USER/ALL/DBA) is 'C'. As for the constraint name, most likely is 'SYSnn..', i.e. ORACLE gave it a name (I have yet to see a table where NOT NULL constraints are explicitly named !).
To get the constraint name and column_name:
SELECT a.constraint_name, b.column_name
FROM user_constraints a, user_cons_columns b
WHERE a.constraint_name=b.constraint_name
AND a.constraint_type='C' AND a.constraint_name like 'SYS%' AND b.position=1 AND a.search_condition LIKE '%NOT NULL%' (I am not sure about this lastcondition, but I think that search_condition column is NOT NULL only for CHECK constraints).
I hope this helps.
Michael Serbanescu
P.S. This is a re-reply, because I got an error when posting the first time. I apologize if my reply may shows up twice (with different texts !)
John Parrott <spo_at_interlog.com> wrote:
>When doing an insert, how can I determine which fields is generating the
>'Not Null column is missing' error? Ideally I'd like to know the name
>of the integrity constraint which raised this message. I know that
>there may be multiple mandatory fields which are blank. I just want one.
>
>I've developed a package which formats error messages given an integrity
>constraint. Now all I need is a way to find the constraint. I'm ok for
>primary key and referential integrity constraints.
>
>When I get this working I might post a copy of this package.
>--
>John Parrott
>Elysium Financial Systems
>spo_at_interlog.com
>http://www.interlog.com/~spo
>
Received on Sun Dec 08 1996 - 00:00:00 CET