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 (See ORA-01442.ora-code.com): 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 ;
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 ;
-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] 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?
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD > <TITLE > </TITLE >
<META http-equiv=Content-Type content= "text/html; charset=iso-8859-1 " >
<META content= "MSHTML 6.00.2800.1400 " name=GENERATOR > </HEAD >
<BODY > <!-- Converted from text/plain format -- >
<P > <FONT size=2 > <FONT face= "Courier New " >Well, you can 't have two "not null "
constraints on a column (see example below showing the error) as far as I
know. <BR >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#. <BR >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.) <BR >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: <BR > <BR >select <BR > c.username || '. ' ||
d.table_name || '. ' || d.column_name as constraint_column, <BR >
b.name || ' (NN) ' as nn_constraint_name, <BR >-- * you don 't really
need this text since we know it will be <BR >-- * "COLUMN_NAME " IS NOT
NULL <BR >-- * a.condition as nn_constraint_text, <BR >
f.name || ' (CHECK) ' as ck_constraint_name, <BR > e.condition as
ck_constraint_text <BR > from <BR > sys.cdef$ a, sys.con$ b,
dba_users c, dba_cons_columns d, <BR > sys.cdef$ e, sys.con$ f,
dba_cons_columns g <BR > where <BR > a.type# = 7 <BR >
and a.cols = 1 <BR > and a.con# = b.con# <BR > and b.owner#
= c.user_id <BR > and c.username = d.owner <BR > and b.name
= d.constraint_name <BR > and e.obj# = a.obj# <BR > and
e.type# = 1 <BR > and e.con# = f.con# <BR > and f.owner# =
b.owner# <BR > and c.username = g.owner <BR > and f.name =
g.constraint_name <BR > and d.column_name = g.column_name <BR >--
* add your specific conditions here from dba_users <BR >-- * and
dba_cons_columns <BR > and c.username = user ; <BR > <BR >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. <BR > <BR >SQL> create table t (n
number not null, d date not null) ; <BR >Table cr����e. <BR >SQL> alter table t add
(check (n is not null)) ; <BR >Table modifi��e. <BR >SQL> alter table t modify (d
not null) ; <BR >alter table t modify (d not
null) <BR >
* <BR >ERREUR �� la ligne 1 : <BR >ORA-01442 (See ORA-01442.ora-code.com): colonne �� modifier en NOT NULL est d��j��
NOT NULL <BR > <BR >SQL> select <BR > 2 c.username ||
'. ' || d.table_name || '. ' || d.column_name as constraint_column, <BR >
3 b.name || ' (NN) ' as nn_constraint_name, <BR >
4 -- * you don 't really need this text since we know it will
be <BR > 5 -- * "COLUMN_NAME " IS NOT NULL <BR >
6 -- * a.condition as nn_constraint_text, <BR >
7 f.name || ' (CHECK) ' as ck_constraint_name, <BR >
8 e.condition as ck_constraint_text <BR >
9 from <BR > 10 sys.cdef$ a, sys.con$ b,
dba_users c, dba_cons_columns d, <BR > 11 sys.cdef$
e, sys.con$ f, dba_cons_columns g <BR > 12
where <BR > 13 a.type# =
7 <BR > 14 and a.cols =
1 <BR > 15 and a.con# =
b.con# <BR > 16 and b.owner# =
c.user_id <BR > 17 and c.username =
d.owner <BR > 18 and b.name =
d.constraint_name <BR > 19 and e.obj# =
a.obj# <BR > 20 and e.type# =
1 <BR > 21 and e.con# =
f.con# <BR > 22 and f.owner# =
b.owner# <BR > 23 and c.username =
g.owner <BR > 24 and f.name =
g.constraint_name <BR > 25 and d.column_name =
g.column_name <BR > 26 -- * add your specific conditions here
from dba_users <BR > 27 -- * and
dba_cons_columns <BR > 28 and c.username = user
; <BR > <BR >CONSTRAINT_COLUMN <BR >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- <BR >NN_CONSTRAINT_NAME
CK_CONSTRAINT_NAME <BR >-- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ----- <BR >CK_CONSTRAINT_TEXT <BR >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- <BR >JRK.T.N <BR >SYS_C005033
(NN)
SYS_C005035 (CHECK) <BR >n is not null <BR > <BR > <BR > <BR >Now if I add another check
constraint on column d, <BR >SQL> alter table t add (check (d > to_date
( '2000/01/01 ', 'YYYY/MM/DD '))) ; <BR >Table modifi��e. <BR > <BR > <BR >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. <BR > <BR >SQL> select <BR >
2 c.username || '. ' || d.table_name || '. ' ||
d.column_name as constraint_column, <BR > 3 b.name
|| ' (NN) ' as nn_constraint_name, <BR > 4 -- * you don 't
really need this text since we know it will be <BR > 5 --
* "COLUMN_NAME " IS NOT NULL <BR > 6 -- * a.condition as
nn_constraint_text, <BR > 7 f.name || ' (CHECK) ' as
ck_constraint_name, <BR > 8 e.condition as
ck_constraint_text <BR > 9
from <BR > 10 sys.cdef$ a, sys.con$ b, dba_users c,
dba_cons_columns d, <BR > 11 sys.cdef$ e, sys.con$ f,
dba_cons_columns g <BR > 12
where <BR > 13 a.type# =
7 <BR > 14 and a.cols =
1 <BR > 15 and a.con# =
b.con# <BR > 16 and b.owner# =
c.user_id <BR > 17 and c.username =
d.owner <BR > 18 and b.name =
d.constraint_name <BR > 19 and e.obj# =
a.obj# <BR > 20 and e.type# =
1 <BR > 21 and e.con# =
f.con# <BR > 22 and f.owner# =
b.owner# <BR > 23 and c.username =
g.owner <BR > 24 and f.name =
g.constraint_name <BR > 25 and d.column_name =
g.column_name <BR > 26 -- * add your specific conditions here
from dba_users <BR > 27 -- * and
dba_cons_columns <BR > 28 and c.username = user
; <BR > <BR >CONSTRAINT_COLUMN <BR >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ <BR >NN_CONSTRAINT_NAME
CK_CONSTRAINT_NAME <BR >-- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ----- <BR >CK_CONSTRAINT_TEXT <BR >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -- <BR >JRK.T.N <BR >SYS_C005033
(NN)
SYS_C005035 (CHECK) <BR >n is not null <BR > <BR >JRK.T.D <BR >SYS_C005034
(NN)
SYS_C005036 (CHECK) <BR >d > to_date ( '2000/01/01 ',
'YYYY/MM/DD ') <BR > </FONT > <BR > <BR > <BR > <BR >-- --Original Message-- -- <BR >From:
oracle-l-bounce@(protected) [ <A
href= "mailto:oracle-l-bounce@(protected) " >mailto:oracle-l-bounce@(protected) </A >]
On Behalf Of Barbara Baker <BR > <BR >I appear to have a bit of a mess on my hands.
I 've <BR >identified some tables that have a duplicate "not <BR >null " constraint on
the same column. Only difference <BR >in the constraints is that one is
generated and one is <BR >user named (even tho they 're both
sys_c00xxx <BR >constraints). <BR > <BR >(I believe this happened when a vendor used a
3rd <BR >party pkg to try to duplicate their schema in
our <BR >database.) <BR > <BR >I 'd like to identify all of the tables with
this <BR >condition. Any method I can think to do this requires <BR >comparing the
search condition of dba_constraints, <BR >which is a LONG. <BR > <BR >Can anyone think
of a way to do this? </FONT > </P > </BODY > </HTML >
