Re: Finding Not Null Constraints

From: Dave Schweisguth <dcs_at_proton.chem.yale.edu>
Date: 1997/04/27
Message-ID: <5k01mj$64r@news.ycc.yale.edu>#1/1

: 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.

You can't do it in SQL alone; you need PL/SQL to parse the long. I chose to copy the constraints views into my own tables, which solves the long problem and also (because of the primary key indices) makes joins inordinately faster. The only drawback is that you need to regenerate the tables as often as your schema changes.

• snip ***
  • Make manageable copies of user_constraints and user_cons_columns

create table uc(

    constraint_name		varchar2(30)	constraint pk_uc primary key,
    constraint_type		varchar2(30),
    table_name			varchar2(30),
    search_condition		varchar2(2000),
    r_constraint_name		varchar2(30)

);
create table ucc(

    constraint_name		varchar2(30),
    table_name			varchar2(30),
    column_name			varchar2(30),

    constraint pk_ucc primary key(constraint_name, table_name, column_name) );

declare

    cursor user_constraints_cur is

	select *
	    from user_constraints;

    cursor user_cons_columns_cur is
	select *
	    from user_cons_columns;

begin

• Fill uc

    for rec in user_constraints_cur loop

	insert into uc
	    (constraint_name, constraint_type, table_name,
		search_condition, r_constraint_name)
	    values(rec.constraint_name, rec.constraint_type, rec.table_name,
		rec.search_condition, rec.r_constraint_name);

    end loop;

• Fill ucc

    for rec in user_cons_columns_cur loop

	insert into ucc
	    (constraint_name, table_name, column_name)
	    values(rec.constraint_name, rec.table_name, rec.column_name);

    end loop;

end;
/

• Check constraints of type 'foo is not null'

select count(*) from uc, ucc where uc.constraint_name = ucc.constraint_name and upper(uc.search_condition) = ucc.column_name || ' IS NOT NULL';

• snip ***

Cheers,

--
| Dave Schweisguth                        For purposes of complying with    |
| dcs_at_proton.chem.yale.edu (MIME OK)      the New Jersey Right to Know Act: |
| http://proton.chem.yale.edu/~dcs/       Contents partially unknown.       |
| Yale Depts. of MB&B & Chemistry   Phone: 203-432-5208   Fax: 203-432-6144 |

Received on Sun Apr 27 1997 - 00:00:00 CDT