Eclectic logo CareersdividersContactdividersSite Map
HomeAbout UsNewsLearning ServicesBusiness Solutions search panel
Banner News
Select an area...

Identify rows in a table which are preventing a constraint being enabled

dividers

Run the utlexcpt.sql script (ORACLE_HOMErdbmsadmin) to create the exceptions table. You can then use this to identify rows in a table which are  preventing a constraint from being enabled

Demo:

SQL> create table t as select employee_id, last_name, salary from employees;

SQL> alter table t add constraint t_pk primary key (employee_id);

SQL> alter table t disable constraint t_pk;

SQL> insert into t values (100,'smith',20000);

SQL> alter table t enable constraint t_pk;
alter table t enable constraint t_pk
*
ERROR at line 1:
ORA-02437: cannot validate (HR.T_PK) - primary key violated
#CREATE THE EXCEPTIONS TABLE
SQL> @?rdbmsadminutlexcpt.sql;

#WE STILL CANNOT VALIDATE THE CONSTRAINT, HOWEVER NOW WE CAN SE WHICH ROWS ARE VIOLATING THE CONSTRAINT
SQL> alter table t enable constraint t_pk exceptions into exceptions;
alter table t enable constraint t_pk exceptions into exceptions
*
ERROR at line 1:
ORA-02437: cannot validate (HR.T_PK) - primary key violated

SQL> select * from exceptions;

ROW_ID             OWNER      TABLE_NAME CONSTRAINT
------------------ ---------- ---------- ----------
AAAHbBAAFAAAEpUAAA HR         T          T_PK
AAAHbBAAFAAAEpXAAA HR         T          T_PK

SQL> select * from t1
2  join exceptions exc
3  on t1.rowid = exc.row_id;

EMPLOYEE_ID LAST_NAME   SALARY ROW_ID             OWNER TABLE_NAME CONSTRAINT
----------- ---------- ---------- ------------------ ----- ---------- -------
100     King        24000 AAAHbBAAFAAAEpUAAA HR   T          T_PK
100     smith       20000 AAAHbBAAFAAAEpXAAA HR   T          T_PK


Newsletter sign up
Sign up now to recieve an email newsletter about Eclectic and our group.
Business Solutions updates
Learning Services newsletter

submit
© Eclectic 2006 | web site designed by Line Terms & ConditionsdividersPrivacy Policy