Exceptions to the offer do not work when creating a constraint

Question

I am working with Oracle 11g.
I have one table:

create table test (one number(2), two number(2)); 

There are 2 lines:

 insert into test (one, two) values (1, 1); insert into test (one, two) values (2, null); commit; 

Now I am creating an exception table:

 create table exceptions(row_id rowid, owner varchar2(30), table_name varchar2(30), constraint varchar2(30)); 

Now I want to create a primary key for the 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 someone help me. thanks in advance

Wolfgang

Answer 1

To do this, you must first disable the restriction:

 ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (one, two) DISABLE; 

Then enable the restriction 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