Home » SQL & PL/SQL » SQL & PL/SQL » constraint error (10g, win8.1,)
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
|
|
|
|
|
|
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...
|
|
|
|
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
|
|
|
|
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:>
|
|
|
Re: constraint error [message #659802 is a reply to message #659801] |
Tue, 31 January 2017 01:14 |
|
Michel Cadot
Messages: 68693 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';
|
|
|
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:>
|
|
|
|
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: 68693 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: 8951 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.
|
|
|
|
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
|
|
|
|
|
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 #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.
|
|
|
Re: constraint error [message #659868 is a reply to message #659867] |
Wed, 01 February 2017 09:39 |
John Watson
Messages: 8951 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.
|
|
|
|
|
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
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 14:05:30 CDT 2024
|