Help with sql identifying dup constraints 2004-06-10 - By Jacques Kilchoer
But the "duplicates " were surely check constraints (sys.cdef$.type# = 1) and there was only one "not null " check constraint (sys.cdef$.type# = 7) Even in 8.1 you could only have one "not null " check constraint (type# = 7). I don 't remember ever trying it on 8.1.4 but it 's definitely true on 8.1.6.
e.g. you get different type# values in sys.cdef$ for constraint X versus constraint Y in this example, even though, looking at dba_constraints, they seem to have identical characteristics.
create table t
(n1 number constraint X not null,
n2 number,
constraint Y check ( "N2 " IS NOT NULL)
) ;
8.1.6
SQL > select * from v$version ;
BANNER
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for Solaris: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production
SQL > create table t (n number not null) ;
Table cr¨¦¨¦e.
SQL > alter table t modify (n not null) ;
alter table t modify (n not null)
*
ERREUR ¨¤ la ligne 1 :
ORA-01442 (See ORA-01442.ora-code.com): column to be modified to NOT NULL is already NOT NULL
__ __
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Duret, Kathy
Sent: jeudi, 10. juin 2004 07:31
To: 'oracle-l@(protected) '
Subject: RE: Help with sql identifying dup constraints
well, you can, at least in 8.1.4
When I came here I found up to 10 not null constraints on the same columns.
Got them all off and found some again.... seemed the other choke ... dba.... didn 't understand that not null was a check constraints and for some reason kept putting them on ... I think via Toad (I never get a straight answer from him) Then we I showed him on some new tables he created all the dups, instead of deleted them, he disabled them all.....
gotta love it. if there is a way in Oracle someone can use and abuse it!
Kathy
-- --Original Message-- --
From: Jacques Kilchoer [mailto:Jacques.Kilchoer@(protected)]
Sent: Wednesday, June 09, 2004 5:23 PM
To: oracle-l@(protected)
Cc: Barbara Baker
Subject: 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.
This transmission contains information solely for intended recipient and may be privileged, confidential and/or otherwise protect from disclosure. If you are not the intended recipient, please contact the sender and delete all copies of this transmission. This message and/or the materials contained herein are not an offer to sell, or a solicitation of an offer to buy, any securities or other instruments. The information has been obtained or derived from sources believed by us to be reliable, but we do not represent that it is accurate or complete. Any opinions or estimates contained in this information constitute our judgment as of this date and are subject to change without notice. Any information you share with us will be used in the operation of our business, and we do not request and do not want any material, nonpublic information. Absent an express prior written agreement, we are not agreeing to treat any information confidentially and will use any and all information and reserve the right to publish or disclose any information you share with us.
<!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 >
<DIV dir=ltr align=left > <SPAN class=147250517-10062004 >But the "duplicates " were
surely check constraints (sys.cdef$.type# = 1) and there was only one "not null "
check constraint (sys.cdef$.type# = 7) Even in 8.1 you could only have one "not
null " check constraint (type# = 7). I don 't remember ever trying it on 8.1.4 but
it 's definitely true on 8.1.6. </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=147250517-10062004 > </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=147250517-10062004 >e.g. you get different
type# values in sys.cdef$ for constraint X versus constraint Y in this
example, even though, looking at dba_constraints, they seem to have identical
characteristics. </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=147250517-10062004 > </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=147250517-10062004 > <FONT face= "Courier New "
size=2 >create table t </FONT > </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=147250517-10062004 > <FONT face= "Courier New "
size=2 > (n1 number constraint X not null, </FONT > </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=147250517-10062004 > <FONT face= "Courier New "
size=2 > n2 number, </FONT > </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=147250517-10062004 > <FONT face= "Courier New "
size=2 > constraint Y check ( "N2 " IS NOT NULL) </FONT > </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=147250517-10062004 > <FONT face= "Courier New "
size=2 > ) ; </FONT > </SPAN > </DIV >
<DIV > </DIV >
<DIV > <FONT face= "Courier New " size=2 > </FONT > </DIV >
<DIV > <SPAN class=147250517-10062004 > <FONT face= "Courier New "
size=2 >8.1.6 </FONT > </SPAN > </DIV >
<DIV > <SPAN class=147250517-10062004 > <FONT face= "Courier New " size=2 >SQL>
select * from v$version ; </FONT > </SPAN > </DIV >
<DIV > <SPAN class=147250517-10062004 > <FONT face= "Courier New "
size=2 >BANNER <BR >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------ <BR >Oracle8i
Enterprise Edition Release 8.1.6.0.0 - Production <BR >PL/SQL Release 8.1.6.0.0 -
Production <BR >CORE
8.1.6.0.0 Production <BR >TNS for Solaris:
Version 8.1.6.0.0 - Production <BR >NLSRTL Version 3.4.0.0.0 -
Production </FONT > </SPAN > </DIV >
<DIV > <FONT face= "Courier New " size=2 > </FONT > </DIV > <SPAN
class=147250517-10062004 >
<DIV > <FONT face= "Courier New " size=2 >SQL> create table t (n number not null)
; </FONT > </DIV >
<DIV > <FONT face= "Courier New " size=2 >Table cr¨¦¨¦e. </FONT > </DIV >
<DIV > <FONT face= "Courier New " size=2 > </FONT > </DIV >
<DIV > <FONT face= "Courier New " size=2 >SQL> alter table t modify (n not null)
; <BR >alter table t modify (n not
null) <BR >
* <BR >ERREUR ¨¤ la ligne 1 : <BR >ORA-01442 (See ORA-01442.ora-code.com): column to be modified to NOT NULL is
already NOT NULL </FONT > </SPAN > </DIV >
<DIV > <BR > </DIV >
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left >
<HR tabIndex=-1 >
<FONT face=Tahoma size=2 > <B >From: </B > oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B >On Behalf Of </B >Duret,
Kathy <BR > <B >Sent: </B > jeudi, 10. juin 2004 07:31 <BR > <B >To: </B >
'oracle-l@(protected) ' <BR > <B >Subject: </B > RE: Help with sql identifying dup
constraints <BR > </FONT > <BR > </DIV >
<DIV > </DIV >
<DIV > <SPAN class=901131513-10062004 > <FONT face=Arial color=#0000ff size=2 >well,
you can, at least in 8.1.4 </FONT > </SPAN > </DIV >
<DIV > <SPAN class=901131513-10062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=901131513-10062004 > <FONT face=Arial color=#0000ff size=2 >When I
came here I found up to 10 not null constraints on the same columns.
</FONT > </SPAN > </DIV >
<DIV > <SPAN class=901131513-10062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=901131513-10062004 > <FONT face=Arial color=#0000ff size=2 >Got
them all off and found some again.... seemed the other choke ... dba.... didn 't
understand that not null was a check constraints and for some reason kept
putting them on ... I think via Toad (I never get a straight answer from
him) Then we I showed him on some new tables he created all the dups,
instead of deleted them, he disabled them all..... </FONT > </SPAN > </DIV >
<DIV > <SPAN class=901131513-10062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=901131513-10062004 > </SPAN > <SPAN class=901131513-10062004 > <FONT
face=Arial color=#0000ff size=2 >gotta love it. if there is a way in Oracle
someone can use and abuse it! </FONT > </SPAN > </DIV >
<DIV > <SPAN class=901131513-10062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<DIV > <SPAN class=901131513-10062004 > <FONT face=Arial color=#0000ff
size=2 >Kathy </FONT > </SPAN > </DIV >
<DIV > <SPAN class=901131513-10062004 > <FONT face=Arial color=#0000ff
size=2 > </FONT > </SPAN > </DIV >
<BLOCKQUOTE dir=ltr style= "MARGIN-RIGHT: 0px " >
<DIV class=OutlookMessageHeader dir=ltr align=left > <FONT face=Tahoma
size=2 >-- --Original Message-- -- <BR > <B >From: </B > Jacques Kilchoer
[mailto:Jacques.Kilchoer@(protected)] <BR > <B >Sent: </B > Wednesday, June 09, 2004
5:23 PM <BR > <B >To: </B > oracle-l@(protected) <BR > <B >Cc: </B > Barbara
Baker <BR > <B >Subject: </B > RE: Help with sql identifying dup
constraints <BR > <BR > </FONT > </DIV > <!-- 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 > </FONT > </FONT > </P > </BLOCKQUOTE > <BR > <BR > <BR >
<P > <FONT face=Arial size=2 >This transmission contains information solely for
intended recipient and may be privileged, confidential and/or otherwise protect
from disclosure. If you are not the intended recipient, please contact the
sender and delete all copies of this transmission. This message and/or the
materials contained herein are not an offer to sell, or a solicitation of an
offer to buy, any securities or other instruments. The information has been
obtained or derived from sources believed by us to be reliable, but we do not
represent that it is accurate or complete. Any opinions or estimates contained
in this information constitute our judgment as of this date and are subject to
change without notice. Any information you share with us will be used in the
operation of our business, and we do not request and do not want any material,
nonpublic information. Absent an express prior written agreement, we are not
agreeing to treat any information confidentially and will use any and all
information and reserve the right to publish or disclose any information you
share with us. </FONT > </P > </BODY > </HTML >
|
|
