Whats Exception Into clause? How Am I able to use it? How it's used to identify check constraint
Exception into clause is used while changing the constraints status to enable validate with duplicate data in the table.After this clause is added all the duplicate data will go in this exception table.
Create this table
create table exceptions(row_id rowid,
ALTER TABLE emp
ENABLE VALIDATE CONSTRAINT pk_emp
EXCEPTIONS INTO exceptions;
Now join this table row_id column with emp tables rowid to get the details of the records.
Hope it helps,
Does it mean that if I enter any record in EMP with duplicate empno then it will add that rowid into EXCEPTIONS table ?
Before running the EXCEPTIONS INTO
1.The table contains duplicate records.
2.The status of constraint is not ENABLE VALIDATE (only then u can insert duplicate records)
After running the EXCEPTION INTO clause in ALTER statement
1.All the duplicate records will go in the exceptions table.
2.After deleting all these records from you main table you can bring the constraint to ENABLE VALIDATE status.
It has nothing to do with insertion of new records.
If you want that previous data to remain duplicate but from new transactions it should check by the constraint then bring the constraint into ENABLE NOVALIDATE state.
Hope its clear now.
Click Here to Expand Forum to Full Width