Re: Finding Not Null Constraints

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/04/27
Message-ID: <33630960.3762@postoffice.worldnet.att.net>#1/1

Sorry about my first reply on using the SEARCH_CONDITION; I did not read your posting carefully enough; otherwise I would have found out that you already tried my solution.

Your other option would be to use the NULLABLE field in the USER_TAB_COLUMNS: SELECT a.table_name||'.'||a.column_name||, DECODE(a.nullable,'N','NOT NULL','CHECK CONSTRAINT')
FROM user_tab_columns a, user_constraints b WHERE a.table_name=b.table_name
AND a.column_name=b.column_name
AND b.constraint_type='C';

Values for NULABLE column are: N if there is a NOT NULL constraint on the table or if the column is part of a PRIMARY KEY constraint; Y if the column accepts NULL values.

The only problem with this approach is if you have both a NOT NULL and a CHECK constraint on that column.

Sorry for my first reply.

Michael Serbanescu

-----------------------------------------------------------------------John Hough wrote:

>
> Can anyone give me a sql query that will distinguish between not null
> constraints and column check constraints.
>
> Both are constraint_type of 'C' and since the search_condition field
> is a "LONG" type we have been unable to utilize it in a where clause.
>
> Help,
>
> John Hough
Received on Sun Apr 27 1997 - 00:00:00 CDT