| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with sql identifying dup constraints
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
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)
*
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 be5 -- * "COLUMN_NAME" IS NOT NULL
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_name26 -- * add your specific conditions here from dba_users 27 -- * and dba_cons_columns
CONSTRAINT_COLUMN
NN_CONSTRAINT_NAME CK_CONSTRAINT_NAMECK_CONSTRAINT_TEXT
----------------------------------- --------------------------------------
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 be5 -- * "COLUMN_NAME" IS NOT NULL
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_name26 -- * add your specific conditions here from dba_users 27 -- * and dba_cons_columns
CONSTRAINT_COLUMN
NN_CONSTRAINT_NAME CK_CONSTRAINT_NAMECK_CONSTRAINT_TEXT
----------------------------------- --------------------------------------
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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?
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Wed Jun 09 2004 - 17:20:32 CDT
-----------------------------------------------------------------
 |
 |