Re: Not Null constraints

From: Michael Serbanescu <mserban>
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 last 
condition, 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

Original text of this message