OraFAQ Forum: SQL & PL/SQL » constraint error

Home » SQL & PL/SQL » SQL & PL/SQL » constraint error (10g, win8.1,)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

constraint error [message #659792]

Tue, 31 January 2017 00:25

rzkhan
Messages: 370
Registered: March 2005

Senior Member

I have a table constraint UK_SIZES which has been DISABLED. But when I insert a record it gives constraint error.

FK_RECEIPTS_CUSTOMERS          RECEIPTS                       R ENABLED
SYS_C008303                    SIZES                          P ENABLED
UK_SIZES                       SIZES                          U DISABLED
SYS_C008305                    SKI_ACCOUNTS                   P ENABLED
PK_SUPP                        SUPPLIERS                      P ENABLED
SYS_C008307                    TRANSPORT_COMPANIES            P ENABLED
UK_W_RATES                     WEAVING_RATES                  U ENABLED
SYS_C008309                    WORKERS                        P ENABLED
SYS_C008310                    WORKERS_TYPES                  P ENABLED



SAM@orcl:> select DBMS_METADATA.GET_DDL('CONSTRAINT', 'UK_SIZES') from dual


SAM@orcl:>/

DBMS_METADATA.GET_DDL('CONSTRAINT','UK_SIZES')
--------------------------------------------------------------------------------

  ALTER TABLE "SAM"."SIZES" ADD CONSTRAINT "UK_SIZES" UNIQUE ("WIDTH", "LENGTH"


SAM@orcl:>select * from sizes;

    SIZEID      WIDTH     LENGTH SIZENAME   SIZET CATEGORY
---------- ---------- ---------- ---------- ----- ----------
       335          4         49 4X49ROLE   RSQFT SIMPLE
       338          4        470 4X470ROLE  RSQFT SIMPLE
       340          4       37.5 4X37.5     RSQFT SIMPLE
       345          4        610 4X610ROLE  RSQFT SIMPLE
       350          4        244 4X244ROLE  RSQFT SIMPLE
       353          4        160 4X160ROLE  RSQFT SIMPLE
       327         16         20 16X20      SQFT  SIMPLE

I want to insert first record with SIZEID 335 replaced with 600.


  1  insert into sizes values(
  2* 600,4,49,'4X49ROLE 2','RSQFT','SIMPLE')
SAM@orcl:>/
insert into sizes values(
*
ERROR at line 1:
ORA-00001: unique constraint (SAM.UK_SIZES) violated


SAM@orcl:>

What is wrong? If anyone can help please.

RZKhan

Report message to a moderator

Re: constraint error [message #659793 is a reply to message #659792]

Tue, 31 January 2017 00:27

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The constraint is not disable.

Report message to a moderator

Re: constraint error [message #659795 is a reply to message #659793]

Tue, 31 January 2017 00:30

rzkhan
Messages: 370
Registered: March 2005

Senior Member

But when I check it shows disabled.

UK_SIZES                       SIZES                          U DISABLED

[Updated on: Tue, 31 January 2017 00:30]

Report message to a moderator

Re: constraint error [message #659796 is a reply to message #659795]

Tue, 31 January 2017 00:39

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is not a constraint from the same schema.

You post many things that are useless because:
1/ The associated command is not posted
2/ The result is truncated

Report message to a moderator

Re: constraint error [message #659797 is a reply to message #659796]

Tue, 31 January 2017 00:46

rzkhan
Messages: 370
Registered: March 2005

Senior Member

I can see the constraint (SAM.UK_SIZES) is from the same schema with DISABLED status.

  1  select owner,constraint_name,table_name,constraint_type,status from
  2    user_constraints
  3*   order by table_name
SAM@orcl:>/

OWNER                          CONSTRAINT_NAME                TABLE_NAME                     C STATUS
------------------------------ ------------------------------ ------------------------------ - -----
SAM                            FK_ACCOUNTS                    ACCOUNT_NOS                    R ENABLED
SAM                            SYS_C008294                    CUSTOMERS                      P ENABLED
SAM                            UK_DEF_RATES_CUST              CUSTOMER_DEFAULT_RATES         U ENABLED
SAM                            FK_ORDDTLS_ORDERS              ORDERDETAILS                   R ENABLED
SAM                            FK_ORDDTLS_SIZES               ORDERDETAILS                   R ENABLED
SAM                            FK_ORDDTLS_SUF_ORDERS          ORDERDETAILS_SUF               R ENABLED
SAM                            FK_ORDERS_CUST                 ORDERS                         R ENABLED
SAM                            SYS_C008296                    ORDERS                         P ENABLED
SAM                            UK_DC_NO_ORDERS                ORDERS                         U ENABLED
SAM                            FK_PAYMENTS                    PAYMENTS                       R ENABLED
SAM                            SYS_C008495                    PRODUCTION                     C ENABLED
SAM                            UK_PRODUCTION                  PRODUCTION                     U ENABLED
SAM                            FK_PRODUCTION_SIZES            PRODUCTION                     R ENABLED
SAM                            PK_PURCH                       PURCHASE                       P ENABLED
SAM                            FK_PO_SUPL                     PURCHASEDETAILS                R ENABLED
SAM                            FK_P_PD                        PURCHASEDETAILS                R ENABLED
SAM                            FK_RAW_ISSUE                   RAW_ISSUE                      R ENABLED
SAM                            FK_ISSUE_INDUSTRY              RAW_ISSUE                      R ENABLED
SAM                            SYS_C008300                    RAW_ISSUE_TO_LIST              P ENABLED
SAM                            FK_RAW_CAT                     RAW_ITEMS                      R ENABLED
SAM                            PK_RAW_ITEMS                   RAW_ITEMS                      P ENABLED
SAM                            PK_RAW_ITEMS_CATEGORIES        RAW_ITEMS_CATEGORIES           P ENABLED
SAM                            FK_RECEIPTS_CUSTOMERS          RECEIPTS                       R ENABLED
SAM                            SYS_C008303                    SIZES                          P ENABLED
SAM                            UK_SIZES                       SIZES                          U DISABLED
SAM                            SYS_C008305                    SKI_ACCOUNTS                   P ENABLED
SAM                            PK_SUPP                        SUPPLIERS                      P ENABLED
SAM                            SYS_C008307                    TRANSPORT_COMPANIES            P ENABLED
SAM                            UK_W_RATES                     WEAVING_RATES                  U ENABLED
SAM                            SYS_C008309                    WORKERS                        P ENABLED
SAM                            SYS_C008310                    WORKERS_TYPES                  P ENABLED

31 rows selected.

SAM@orcl:>

let me remove useless things.

 1  insert into sizes values(
  2* 600,4,49,'4X49ROLE 2','RSQFT','SIMPLE')
SAM@orcl:>/
insert into sizes values(
*
ERROR at line 1:
ORA-00001: unique constraint (SAM.UK_SIZES) violated


SAM@orcl:>

Please advise if I am wrong...

Report message to a moderator

Re: constraint error [message #659798 is a reply to message #659797]

Tue, 31 January 2017 00:50

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select * from v$version;

What is the COMPLETE definition of the constraint, not just:
ALTER TABLE "SAM"."SIZES" ADD CONSTRAINT "UK_SIZES" UNIQUE ("WIDTH", "LENGTH"
what is the rest?

Report message to a moderator

Re: constraint error [message #659799 is a reply to message #659798]

Tue, 31 January 2017 00:56

rzkhan
Messages: 370
Registered: March 2005

Senior Member

SAM@orcl:>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

complete definition is

select DBMS_METADATA.GET_DDL('CONSTRAINT', 'UK_SIZES') from dual

 ALTER TABLE "SAM"."SIZES" ADD CONSTRAINT "UK_SIZES" UNIQUE ("WIDTH", "LENGTH", "CATEGORY") DISABLE

Report message to a moderator

Re: constraint error [message #659800 is a reply to message #659799]

Tue, 31 January 2017 01:01

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ALTER TABLE "SAM"."SIZES" disable CONSTRAINT "UK_SIZES"; 
ALTER TABLE "SAM"."SIZES" enable CONSTRAINT "UK_SIZES";
INSERT ...

Report message to a moderator

Re: constraint error [message #659801 is a reply to message #659800]

Tue, 31 January 2017 01:05

rzkhan
Messages: 370
Registered: March 2005

Senior Member

same result

SAM@orcl:>
SAM@orcl:>ALTER TABLE "SAM"."SIZES" disable CONSTRAINT "UK_SIZES"; 

Table altered.

SAM@orcl:>ALTER TABLE "SAM"."SIZES" enable CONSTRAINT "UK_SIZES";

Table altered.

SAM@orcl:>insert into sizes values (600,4,49,'4X49ROLE 2','RSQFT','SIMPLE')
  2  /
insert into sizes values (600,4,49,'4X49ROLE 2','RSQFT','SIMPLE')
*
ERROR at line 1:
ORA-00001: unique constraint (SAM.UK_SIZES) violated


SAM@orcl:>ALTER TABLE "SAM"."SIZES" disable CONSTRAINT "UK_SIZES"; 

Table altered.

SAM@orcl:>insert into sizes values (600,4,49,'4X49ROLE 2','RSQFT','SIMPLE');
insert into sizes values (600,4,49,'4X49ROLE 2','RSQFT','SIMPLE')
*
ERROR at line 1:
ORA-00001: unique constraint (SAM.UK_SIZES) violated


SAM@orcl:>

Report message to a moderator

Re: constraint error [message #659802 is a reply to message #659801]

Tue, 31 January 2017 01:14

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select index_name, index_type, uniqueness from all_tables where table_owner='SAM' and table_name='SIZES';
select owner, constraint_name, constraint_type, status, generated, index_owner, index_name, invalid 
from all_constraints where table_name='SIZES';

Report message to a moderator

Re: constraint error [message #659803 is a reply to message #659802]

Tue, 31 January 2017 01:36

rzkhan
Messages: 370
Registered: March 2005

Senior Member

all_tables dont have such attributes

desc below


SAM@orcl:>desc all_tables;
 Name                                                                                                              Null?    Type
 ---------------------------------------------------------------------------------------------------
 OWNER                                                                                                             NOT NULL VARCHAR2(30)
 TABLE_NAME                                                                                                        NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                                                                                            VARCHAR2(30)
 CLUSTER_NAME                                                                                                               VARCHAR2(30)
 IOT_NAME                                                                                                                   VARCHAR2(30)
 STATUS                                                                                                                     VARCHAR2(8)
 PCT_FREE                                                                                                                   NUMBER
 PCT_USED                                                                                                                   NUMBER
 INI_TRANS                                                                                                                  NUMBER
 MAX_TRANS                                                                                                                  NUMBER
 INITIAL_EXTENT                                                                                                             NUMBER
 NEXT_EXTENT                                                                                                                NUMBER
 MIN_EXTENTS                                                                                                                NUMBER
 MAX_EXTENTS                                                                                                                NUMBER
 PCT_INCREASE                                                                                                               NUMBER
 FREELISTS                                                                                                                  NUMBER
 FREELIST_GROUPS                                                                                                            NUMBER
 LOGGING                                                                                                                    VARCHAR2(3)
 BACKED_UP                                                                                                                  VARCHAR2(1)
 NUM_ROWS                                                                                                                   NUMBER
 BLOCKS                                                                                                                     NUMBER
 EMPTY_BLOCKS                                                                                                               NUMBER
 AVG_SPACE                                                                                                                  NUMBER
 CHAIN_CNT                                                                                                                  NUMBER
 AVG_ROW_LEN                                                                                                                NUMBER
 AVG_SPACE_FREELIST_BLOCKS                                                                                                  NUMBER
 NUM_FREELIST_BLOCKS                                                                                                        NUMBER
 DEGREE                                                                                                                     VARCHAR2(10)
 INSTANCES                                                                                                                  VARCHAR2(10)
 CACHE                                                                                                                      VARCHAR2(5)
 TABLE_LOCK                                                                                                                 VARCHAR2(8)
 SAMPLE_SIZE                                                                                                                NUMBER
 LAST_ANALYZED                                                                                                              DATE
 PARTITIONED                                                                                                                VARCHAR2(3)
 IOT_TYPE                                                                                                                   VARCHAR2(12)
 TEMPORARY                                                                                                                  VARCHAR2(1)
 SECONDARY                                                                                                                  VARCHAR2(1)
 NESTED                                                                                                                     VARCHAR2(3)
 BUFFER_POOL                                                                                                                VARCHAR2(7)
 ROW_MOVEMENT                                                                                                               VARCHAR2(8)
 GLOBAL_STATS                                                                                                               VARCHAR2(3)
 USER_STATS                                                                                                                 VARCHAR2(3)
 DURATION                                                                                                                   VARCHAR2(15)
 SKIP_CORRUPT                                                                                                               VARCHAR2(8)
 MONITORING                                                                                                                 VARCHAR2(3)
 CLUSTER_OWNER                                                                                                              VARCHAR2(30)
 DEPENDENCIES                                                                                                               VARCHAR2(8)
 COMPRESSION                                                                                                                VARCHAR2(8)
 DROPPED                                                                                                                    VARCHAR2(3)

SAM@orcl:>select owner, constraint_name, constraint_type, status, generated, index_owner, index_name
, invalid 
  2  from all_constraints where table_name='SIZES';

OWNER                          CONSTRAINT_NAME                C STATUS   GENERATED      INDEX_OWNER                    INDEX_NAME                     INVA
------------------------------ ------------------------------ - -------- -------------- ------------
SYSTEM                         SYS_C005896                    P ENABLED  GENERATED NAME                                SYS_C005896
SAM                            SYS_C008303                    P ENABLED  GENERATED NAME                                SYS_C008303
SAM                            UK_SIZES                       U DISABLED USER NAME

SAM@orcl:>

Report message to a moderator

Re: constraint error [message #659804 is a reply to message #659803]

Tue, 31 January 2017 01:42

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Sorry I mean ALL_INDEXES.
Adjust the columns to have them on a single line with no more than 120 characters.
Reexecute both queries (modifying the first one).

Report message to a moderator

Re: constraint error [message #659805 is a reply to message #659804]

Tue, 31 January 2017 01:49

rzkhan
Messages: 370
Registered: March 2005

Senior Member


select INDEX_NAME,INDEX_TYPE,UNIQUENESS
from ALL_INDEXES
where owner='SAM' and table_name='SIZES';

UK_SIZES	NORMAL	UNIQUE
SYS_C008303	NORMAL	UNIQUE


select owner, constraint_name, constraint_type, status, generated, index_owner, index_name, invalid 
from all_constraints where table_name='SIZES';

SYSTEM	SYS_C005896	P	ENABLED	GENERATED NAME		SYS_C005896	
SAM	SYS_C008303	P	ENABLED	GENERATED NAME		SYS_C008303	
SAM	UK_SIZES	U	DISABLED	USER NAME

[Updated on: Tue, 31 January 2017 01:50]

Report message to a moderator

Re: constraint error [message #659806 is a reply to message #659805]

Tue, 31 January 2017 01:54

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Final check:

select index_owner, index_name, column_name from all_ind_columns where index_name in ('UK_SIZES','SYS_C008303') order by index_owner, index_name, column_position;

[Updated on: Tue, 31 January 2017 01:56]

Report message to a moderator

Re: constraint error [message #659807 is a reply to message #659805]

Tue, 31 January 2017 01:55

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I can simulate the effect by (rather foolishly) creating a unique index in a different schema with the same name a the constraint:

orclz> sho user
USER is "SYSTEM"
orclz> create unique index system.uk_ename on scott.emp(ename);

Index created.

orclz> alter table scott.emp add constraint uk_ename unique(ename);

Table altered.

orclz> alter table scott.emp disable constraint uk_ename;

Table altered.

orclz> update scott.emp set ename='KING' where ename='FORD';
update scott.emp set ename='KING' where ename='FORD'
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.UK_ENAME) violated


orclz>

Is that what you have done? I think this is what Michel is working towards.

Report message to a moderator

Re: constraint error [message #659808 is a reply to message #659807]

Tue, 31 January 2017 01:56

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, this is something like that. Smile

Report message to a moderator

Re: constraint error [message #659809 is a reply to message #659806]

Tue, 31 January 2017 01:58

rzkhan
Messages: 370
Registered: March 2005

Senior Member

select index_name, column_name from all_ind_columns where index_name in ('UK_SIZES','SYS_C008303') order by index_name, column_position;


SYS_C008303	SIZEID
UK_SIZES	WIDTH
UK_SIZES	LENGTH
UK_SIZES	CATEGORY

Report message to a moderator

Re: constraint error [message #659810 is a reply to message #659807]

Tue, 31 January 2017 01:59

rzkhan
Messages: 370
Registered: March 2005

Senior Member

@ John Watson

I am also trying to prove myself foolish... Laughing

[Updated on: Tue, 31 January 2017 02:00]

Report message to a moderator

Re: constraint error [message #659811 is a reply to message #659810]

Tue, 31 January 2017 02:05

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Drop the index "UK_SIZES" and it will work now.

Report message to a moderator

Re: constraint error [message #659812 is a reply to message #659811]

Tue, 31 January 2017 02:14

rzkhan
Messages: 370
Registered: March 2005

Senior Member

same error.

SAM@orcl:>
SAM@orcl:>alter table sizes drop  CONSTRAINT UK_SIZES;

Table altered.

SAM@orcl:>
SAM@orcl:>
SAM@orcl:>insert into sizes values(600,4,49,'4X49ROLE 2','RSQFT','SIMPLE');
insert into sizes values(600,4,49,'4X49ROLE 2','RSQFT','SIMPLE')
*
ERROR at line 1:
ORA-00001: unique constraint (SAM.UK_SIZES) violated

I also tried this option last night. Then I came here....

[Updated on: Tue, 31 January 2017 02:21]

Report message to a moderator

Re: constraint error [message #659813 is a reply to message #659812]

Tue, 31 January 2017 02:24

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

You have not dropped the index, oonly the constraint.

Report message to a moderator

Re: constraint error [message #659814 is a reply to message #659813]

Tue, 31 January 2017 02:34

rzkhan
Messages: 370
Registered: March 2005

Senior Member

ooh


SAM@orcl:>drop index uk_sizes;

Index dropped.

SAM@orcl:>insert into sizes values(600,4,49,'4X49ROLE 2','RSQFT','SIMPLE');

1 row created.

SAM@orcl:>

Thanks a lot for all your help. GREAT !!!!
priceless knowledge. I should read in detail constraints vs indexes

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3051352977204

[Updated on: Tue, 31 January 2017 02:40]

Report message to a moderator

Re: constraint error [message #659819 is a reply to message #659814]

Tue, 31 January 2017 03:02

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for the feedback and link, they will help future readers.

Report message to a moderator

Re: constraint error [message #659867 is a reply to message #659819]

Wed, 01 February 2017 09:29

Bill B
Messages: 1971
Registered: December 2004

Senior Member

oracle can only enforce a unique constraint with a unique index. If there is no index already existing for the columns, oracle will build one and remove it if the constraint is removed or disabled. If the index already exists oracle will use it and will NOT remove the index if the constraint is dropped or disabled.

Report message to a moderator

Re: constraint error [message #659868 is a reply to message #659867]

Wed, 01 February 2017 09:39

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I think a case can be made for always pre-creating the index. The index and the constraint are different things, so declaring them separately makes sense to me. Furthermore, if the index is created as nonunique, it gives you more options later: the constraint can be deferred or disabled or novalidated without dropping the index. Sure, there is an extra block visit when using a nonunique index but that is probasbly a price worth paying.

Perhaps unique indexes are just a hang-over from release 6, when constraints didn't exist.

Report message to a moderator

Re: constraint error [message #659869 is a reply to message #659867]

Wed, 01 February 2017 09:50

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Unique index is not mandatory it could be a non unique index:

SQL> create table t (id int);

Table created.

SQL> create index t_idx on t (id);

Index created.

SQL> select uniqueness from user_indexes where index_name = 'T_IDX';
UNIQUENES
---------
NONUNIQUE

1 row selected.

SQL> alter table t add constraint t_unq unique (id);

Table altered.

SQL> select constraint_name, index_name from user_constraints where table_name = 'T';
CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------
T_UNQ                          T_IDX

1 row selected.

Is there anyone knowing how we can differentiate the 2 cases (will it drop or not the index when dropping the constraint)?

Report message to a moderator

Re: constraint error [message #659870 is a reply to message #659868]

Wed, 01 February 2017 09:52

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

John Watson wrote on Wed, 01 February 2017 16:39

Perhaps unique indexes are just a hang-over from release 6, when constraints didn't exist.

This is a sensible guess.

Report message to a moderator

Re: constraint error [message #659873 is a reply to message #659870]

Wed, 01 February 2017 10:20

Bill B
Messages: 1971
Registered: December 2004

Senior Member

You have to run this as the SYS user but the following code will return KEEP or DROP to show what would happen to the indexes in a specified schema if the constraint is dropped.

SELECT Do.Object_name,
       CASE
           WHEN BITAND (Ind$.Property, 4096) = 4096 THEN 'DROP'
           ELSE 'KEEP'
       END,
       Ind$.Property
  FROM Sys.Ind$ Ind$, Dba_objects Do
 WHERE     Do.Owner = 'SCHEMA_NAME'
       AND Do.Object_id = Ind$.Obj#;

[Updated on: Wed, 01 February 2017 10:23]

Report message to a moderator

Re: constraint error [message #659875 is a reply to message #659873]

Wed, 01 February 2017 10:28

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks.

Report message to a moderator

Previous Topic:	Help with update statement
Next Topic:	order of operations in the below statements

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:21:45 CDT 2024