Exceptions into clause does not work when creating a constraint
oracle
Solution
In order to do this, you must first create the constraint disabled:
ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (one, two) DISABLE;
Then, enable the constraint with exceptions:
ALTER TABLE TEST ENABLE CONSTRAINT test_pk EXCEPTIONS INTO exceptions;
Then you can select the results:
SQL> SELECT * FROM EXCEPTIONS;
ROW_ID OWNER TABLE_NAME CONSTRAINT
------------------ ----- ---------- ----------
AAHpV4AAHAAApliAAB XXX TEST TEST_PK
Problem
I work with Oracle 11g. I have one table: ``` create table test (one number(2), two number(2)); ``` There are 2 rows: ``` insert into test (one, two) values (1, 1); insert into test (one, two) values (2, null); commit; ``` Now I create an exceptions table: ``` create table exceptions(row_id rowid, owner varchar2(30), table_name varchar2(30), constraint varchar2(30)); ``` Now I want to create the primary key for test: ``` alter table test add constraint test_pk primary key (one, two) exceptions into exceptions; ``` Of course I get the following error: ORA-01449 But the row that caused the exception is not in the exception table? Can anybody help me. Thanks in advance Wolfgang