RE: Help with sql identifying dup constraints
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Jun 2004 15:23:21 -0700
Well, you can't have two "not null" constraints on a column (see example below
showing the error) as far as I know.
What I think you have is one "NOT NULL" constraint and one CHECK constraint.
From the DBA_ views NOT NULL constraints and CHECK constraints look the same,
but in SYS.CDEF$ they have different values for TYPE#.
As you noticed you can't compare the constraint text in SQL since it's stored
in a LONG. (Shouldn't this column have been changed to a CLOB in 9.2? But I
digress.)
I would write a query that looks for a NOT NULL constraint (which can only have
one column), and then look for a CHECK constraint that only has one column and
the same column as the not null constraint. This should help narrow your search
down, and by looking at the text for the CHECK constraint you could easily
figure out which ones are duplicates of a not null constraint. E.g. something
like this:
select
c.username || '.' || d.table_name || '.' || d.column_name as
constraint_column,
b.name || ' (NN)' as nn_constraint_name,
-- * you don't really need this text since we know it will be
-- * "COLUMN_NAME" IS NOT NULL
-- * a.condition as nn_constraint_text,
f.name || ' (CHECK)' as ck_constraint_name,
e.condition as ck_constraint_text
from
sys.cdef$ a, sys.con$ b, dba_users c, dba_cons_columns d,
sys.cdef$ e, sys.con$ f, dba_cons_columns g
where
a.type# = 7
and a.cols = 1
and a.con# = b.con#
and b.owner# = c.user_id
and c.username = d.owner
and b.name = d.constraint_name
and e.obj# = a.obj#
and e.type# = 1
and e.con# = f.con#
and f.owner# = b.owner#
and c.username = g.owner
and f.name = g.constraint_name
and d.column_name = g.column_name
-- * add your specific conditions here from dba_users
-- * and dba_cons_columns
and c.username = user ;
Example: I try to create a table with two NOT NULL constraints on the same
column - Oracle error. However I can create a column with a NOT NULL constraint
and a similar CHECK constraint. My query will show this.
SQL> create table t (n number not null, d date not null) ;
Table créée.
SQL> alter table t add (check (n is not null)) ;
Table modifiée.
SQL> alter table t modify (d not null) ;
alter table t modify (d not null)
*
ERREUR à la ligne 1 :
ORA-01442: colonne à modifier en NOT NULL est déjà NOT NULL
SQL> select
2 c.username || '.' || d.table_name || '.' || d.column_name as
constraint_column,
3 b.name || ' (NN)' as nn_constraint_name,
4 -- * you don't really need this text since we know it will be
5 -- * "COLUMN_NAME" IS NOT NULL
6 -- * a.condition as nn_constraint_text,
7 f.name || ' (CHECK)' as ck_constraint_name,
8 e.condition as ck_constraint_text
9 from
10 sys.cdef$ a, sys.con$ b, dba_users c, dba_cons_columns d,
11 sys.cdef$ e, sys.con$ f, dba_cons_columns g
12 where
13 a.type# = 7
14 and a.cols = 1
15 and a.con# = b.con#
16 and b.owner# = c.user_id
17 and c.username = d.owner
18 and b.name = d.constraint_name
19 and e.obj# = a.obj#
20 and e.type# = 1
21 and e.con# = f.con#
22 and f.owner# = b.owner#
23 and c.username = g.owner
24 and f.name = g.constraint_name
25 and d.column_name = g.column_name
26 -- * add your specific conditions here from dba_users
27 -- * and dba_cons_columns
28 and c.username = user ;
CONSTRAINT_COLUMN
-----------------------------------------------------------------------------------------------
NN_CONSTRAINT_NAME CK_CONSTRAINT_NAME
----------------------------------- --------------------------------------
CK_CONSTRAINT_TEXT
--------------------------------------------------------------------------------
JRK.T.N
SYS_C005033 (NN) SYS_C005035 (CHECK)
n is not null
Now if I add another check constraint on column d,
SQL> alter table t add (check (d > to_date ('2000/01/01', 'YYYY/MM/DD'))) ;
Table modifiée.
We will see that the query will show a NOT NULL constraint on column d and also
a check constraint on column d, but the text of the constraint is enough to
tell us that we don't have a "duplicate" not null constraint.
SQL> select
2 c.username || '.' || d.table_name || '.' || d.column_name as
constraint_column,
3 b.name || ' (NN)' as nn_constraint_name,
4 -- * you don't really need this text since we know it will be
5 -- * "COLUMN_NAME" IS NOT NULL
6 -- * a.condition as nn_constraint_text,
7 f.name || ' (CHECK)' as ck_constraint_name,
8 e.condition as ck_constraint_text
9 from
10 sys.cdef$ a, sys.con$ b, dba_users c, dba_cons_columns d,
11 sys.cdef$ e, sys.con$ f, dba_cons_columns g
12 where
13 a.type# = 7
14 and a.cols = 1
15 and a.con# = b.con#
16 and b.owner# = c.user_id
17 and c.username = d.owner
18 and b.name = d.constraint_name
19 and e.obj# = a.obj#
20 and e.type# = 1
21 and e.con# = f.con#
22 and f.owner# = b.owner#
23 and c.username = g.owner
24 and f.name = g.constraint_name
25 and d.column_name = g.column_name
26 -- * add your specific conditions here from dba_users
27 -- * and dba_cons_columns
28 and c.username = user ;
CONSTRAINT_COLUMN
----------------------------------------------------------------------------------------------
NN_CONSTRAINT_NAME CK_CONSTRAINT_NAME
----------------------------------- --------------------------------------
CK_CONSTRAINT_TEXT
--------------------------------------------------------------------------------
JRK.T.N
SYS_C005033 (NN) SYS_C005035 (CHECK)
n is not null
JRK.T.D
SYS_C005034 (NN) SYS_C005036 (CHECK)
d > to_date ('2000/01/01', 'YYYY/MM/DD')
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Barbara Baker
I appear to have a bit of a mess on my hands. I've
identified some tables that have a duplicate "not
null" constraint on the same column. Only difference
in the constraints is that one is generated and one is
user named (even tho they're both sys_c00xxx
constraints).
(I believe this happened when a vendor used a 3rd
party pkg to try to duplicate their schema in our
database.)
I'd like to identify all of the tables with this
condition. Any method I can think to do this requires
comparing the search condition of dba_constraints,
which is a LONG.
Can anyone think of a way to do this?
Other related posts:
