CONSTRAINT ERROR Find What data creating problem

d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\admin\utlexcpt.sql

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

if someone disable the constraint and try to enable, it show error exceptions table stores the table and rowid

 Alter Table <table_name>  Enable constraint <ck_constriantname> exceptions into exceptions;

Check Constraint on SYSDATE

create table table1 (startdate date,CloseDate date);

ALTER TABLE Table1
ADD (CONSTRAINT GT_Table1_CloseDate
CHECK (CloseDate > SYSDATE),
CONSTRAINT LT_Table1_CloseDate
CHECK (CloseDate <= SYSDATE + 365)),
CONSTRAINT GT_Table1_StartDate
CHECK (StartDate > (CloseDate + (SYSDATE + 730))));



Error report:
SQL Error: ORA-02436: date or system variable wrongly specified in CHECK constraint
02436. 00000 -  "date or system variable wrongly specified in CHECK constraint"
*Cause:    An attempt was made to use a date constant or system variable,
           such as USER, in a check constraint that was not completely
           specified in a CREATE TABLE or ALTER TABLE statement.  For
           example, a date was specified without the century.
*Action:   Completely specify the date constant or system variable.
           Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",
           which a bug permitted to be created before version 8.




A check constraint, unfortunately, cannot reference a function like SYSDATE. You would need to create a trigger that checked these values when DML occurs, i.e.

CREATE OR REPLACE TRIGGER trg_check_dates
  BEFORE INSERT OR UPDATE ON table1
  FOR EACH ROW
BEGIN
  IF( :new.CloseDate <= SYSDATE )
  THEN
    RAISE_APPLICATION_ERROR( -20001,
          'Invalid CloseDate: CloseDate must be greater than the current date - value = ' ||
          to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.CloseDate > add_months(SYSDATE,12) )
  THEN
    RAISE_APPLICATION_ERROR( -20002,
         'Invalid CloseDate: CloseDate must be within the next year - value = ' ||
         to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.StartDate <= add_months(:new.CloseDate,24) )
  THEN
    RAISE_APPLICATION_ERROR( -20002,
          'Invalid StartDate: StartDate must be within 24 months of the CloseDate - StartDate = ' ||
          to_char( :new.StartDate, 'YYYY-MM-DD HH24:MI:SS' ) ||
          ' CloseDate = ' || to_char( :new.CloseDate , 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
END;
/


Each and every time the record is updated SYSDATE will have a different value. Therefore the constraint will validate differently each time. Oracle does not allow sysdate in a constraint for that reason.

You may be able to solve your problem with a trigger that checks if CloseDate has actually changed and raise an exception when the new value is not within range.

ORA-02290 check constraint violated

Select 'SELECT * FROM ' || Table_Name || ' MINUS SELECT * FROM ' || Table_Name || ' WHERE ' || Search_Condition || ';'  From User_Constraints Where Constraint_Type='C'
AND Constraint_Name Like '%CK%LEDGERAM%';
----AND TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE Data_Type NOT LIKE '%LONG%');

ORA-00932: inconsistent datatypes: expected NUMBER got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"


spool c:\constr1.txt
set line 9999
set serveroutput on
BEGIN
FOR r IN (
SELECT cns.table_name, cns.constraint_name
    , cns.search_condition
FROM   user_constraints cns
    , user_cons_columns col
WHERE  cns.constraint_type = 'C'
AND    col.owner = cns.owner
AND    col.table_name = cns.table_name
AND    col.constraint_name = cns.constraint_name
)
LOOP
DBMS_OUTPUT.PUT_LINE('SELECT * FROM ' || r.Table_Name || ' MINUS SELECT * FROM ' || r.Table_Name || ' WHERE ' || r.Search_Condition || ';');
END LOOP;
END;
/
spool off

ORA-02291: integrity constraint (string.string) violated - parent key not found

ORA-02291: integrity constraint (string.string) violated - parent key not found

Cause: A foreign key value has no matching primary key value.

Action: Delete the foreign key or add a matching primary key.

For an insert statement, this ORA-02291 error is common when you are trying to insert a child without a matching parent, as defined by a foreign key constraint.  In that case, you need to add the parent row to the table and then re-insert your child table row.




SELECT   DISTINCT uc.constraint_name||CHR(10)
||      '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
And      Uc.Constraint_Type = 'R'
----AND      uc.constraint_name = UPPER('&input_constraint_name');
AND      uc.constraint_name = 'FK_CHARGESCLIENT';



Select Distinct Cfirmnumber,Cpostingaccount From Tblclientchargesdetail
Minus
Select Distinct FIRMNUMBER,OOWNCODE From ACCOUNTS;


add the missing row into table.


---------------------------------------------------------------------------------------------------------------------------------------
 Select 'SELECT DISTINCT ' || Columns1 || ' from ' ||tablename || ' minus ' || 'SELECT DISTINCT ' || Columns2 || ' from ' || r_table_name || ';'
 From
 (
 select tablename,r_table_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
Nvl2(Cname7,','||Cname7,Null) || Nvl2(Cname8,','||Cname8,Null)
Columns1,
cname9 || nvl2(cname10,','||cname10,null) ||
Nvl2(Cname11,','||Cname11,Null) || Nvl2(Cname12,','||Cname12,Null) ||
Nvl2(Cname13,','||Cname13,Null) || Nvl2(Cname14,','||Cname14,Null) ||
Nvl2(Cname15,','||Cname15,Null) || Nvl2(Cname16,','||Cname16,Null) Columns2
From (
 Select B.Table_Name Tablename,
b.Constraint_Name,
Max(Decode( A.Position, 1, A.Column_Name, Null )) Cname1,
Max(Decode( A.Position, 2, A.Column_Name, Null )) Cname2,
Max(Decode( A.Position, 3, A.Column_Name, Null )) Cname3,
Max(Decode( A.Position, 4, A.Column_Name, Null )) Cname4,
Max(Decode( A.Position, 5, A.Column_Name, Null )) Cname5,
Max(Decode( A.Position, 6, A.Column_Name, Null )) Cname6,
Max(Decode( A.Position, 7, A.Column_Name, Null )) Cname7,
Max(Decode( A.Position, 8, A.Column_Name, Null )) Cname8,
c.Table_Name r_table_name,
Max(Decode( C.Position, 1, C.Column_Name, Null )) Cname9,
Max(Decode( C.Position, 2, C.Column_Name, Null )) Cname10,
Max(Decode( C.Position, 3, C.Column_Name, Null )) Cname11,
Max(Decode( C.Position, 4, C.Column_Name, Null )) Cname12,
Max(Decode( C.Position, 5, C.Column_Name, Null )) Cname13,
Max(Decode( C.Position, 6, C.Column_Name, Null )) Cname14,
Max(Decode( C.Position, 7, C.Column_Name, Null )) Cname15,
Max(Decode( C.Position, 8, C.Column_Name, Null )) Cname16
From User_Cons_Columns a,user_constraints b,User_Cons_Columns c
Where A.Constraint_Name = B.Constraint_Name
And B.R_Constraint_Name=C.Constraint_Name
And B.Constraint_Type = 'R'
And B.Constraint_Name='FK_CHARGESCLIENT'
Group By B.Table_Name, B.Constraint_Name,C.Table_Name
));

-----------------------------------

Find Duplicate records (Unique Index columns) in all Tables

SELECT 'SELECT COUNT(*),' || wm_concat(column_name) || ' from ' ||table_name || ' group by ' || wm_concat(column_name) || ' having count(*)>1;'

FROM user_ind_columns where index_name in (select index_name from user_indexes where uniqueness='UNIQUE')

GROUP BY index_name,table_name;

Find and Delete duplicate check Constraints

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

-- Find duplicate check constraints in current user's schema that have Oracle

-- generated names (like 'SYS_C%'). A script (c:\drop_duplicate_constraints.sql)

-- is generated to drop those duplicates.

--

-- This removes those duplicated constraints that were probably created by

-- improper usage of the Oracle imp utility. Using CONSTRAINTS=N will not

-- create duplicate constraints when importing into an existing table.

--

-- Tables js_constraints and js_constraints_min are dropped and creaed

-- the current user's schema, drop them to clean up.

--

-- WARNING: Review this and the generated script

-- c:\drop_duplicate_constraints.sql before running it at YOUR RISK.

-- I do not accept any responsibility for what YOU DO TO YOUR DATABASES !

--

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

set linesize 120

set pagesize 50000

set timing on

drop table js_constraints

/

create table js_constraints

(

owner varchar2(30),

constraint_name varchar2(30),

table_name varchar2(30),

search_condition varchar2(2000)

)

/

declare

cursor js_cursor is

select

owner,

table_name,

constraint_name,

search_condition

from user_constraints

where owner = user

and constraint_type = 'C'

and constraint_name like 'SYS_C%';

js_rec js_cursor%rowtype;

commit_interval constant integer := 10000;

records_processed integer := 0;

begin

open js_cursor;

loop

fetch js_cursor into js_rec;

exit when js_cursor%notfound;

insert into js_constraints (

owner,

table_name,

constraint_name,

search_condition)

values (

js_rec.owner,

js_rec.table_name,

js_rec.constraint_name,

substr(js_rec.search_condition,1,2000)

);

records_processed := records_processed + 1;

if records_processed = commit_interval then

commit;

records_processed := 0;

end if;

end loop;

commit;

close js_cursor;

end;

/

create index js_constraints_x1 on js_constraints (owner, table_name, search_condition)

/

drop table js_constraints_min

/

create table js_constraints_min as

select

owner,

table_name,

search_condition,

min(translate(constraint_name, 'SYC_', '9999')) as min_constraint_name,

'at least 30 dummy characters !!!' as constraint_name

from js_constraints

group by owner, table_name, search_condition

/

update js_constraints_min cm

set constraint_name = (select constraint_name from js_constraints c

where c.owner = cm.owner

and c.table_name = cm.table_name

and translate(c.constraint_name, 'SYC_', '9999') = cm.min_constraint_name)

/

delete from js_constraints

where (owner, table_name, search_condition, translate(constraint_name, 'SYC_', '9999')) in

(select owner, table_name, search_condition, min_constraint_name

from js_constraints_min)

/

-------spool c:\js_drop_duplicate_constraints.sql

select

'alter table ' || c.owner || '.' || c.table_name ||

' drop constraint ' || c.constraint_name ||

' --duplicate of ' || cm.constraint_name || chr(13) || '/'

from js_constraints c, js_constraints_min cm

where c.owner = cm.owner

and c.table_name = cm.table_name

and c.search_condition = cm.search_condition

order by c.owner, c.table_name, cm.min_constraint_name, c.constraint_name

/

spool off

Primary key Constraints Enable

SQL> alter table CLJOBB disable constraint CLJOBB;

associated index (CLJOBB) will be dropped automatically.



SQL> alter table CLJOBB enable constraint CLJOBB;
alter table CLJOBB enable constraint CLJOBB
*
ERROR at line 1:
ORA-02437: cannot validate (LDBO.CLJOBB) - primary key violated




Enabling of the PK constraint requires association with index.
If we now try to enable the PK constraint again, it will pick up the first index it found on that column and will get associated with it. In case there is no index to get associated, oracle will create a new index with the name same as that of PK constraint.



CREATE UNIQUE INDEX "LDBO"."CLJOBB" ON "LDBO"."CLJOBB" ("FIRMNUMBER", "NFINANCIALYEAR", "CODE", "EXCHANGE", "BOOKCODE", "DVALIDUPTO", "NPRODUCTCODE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 104857600 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" ;



ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

CREATE INDEX "LDBO"."CLJOBB" ON "LDBO"."CLJOBB" ("FIRMNUMBER", "NFINANCIALYEAR", "CODE", "EXCHANGE", "BOOKCODE", "DVALIDUPTO", "NPRODUCTCODE") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 104857600 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDX" ;






SQL> alter table CLJOBB enable constraint CLJOBB;
alter table CLJOBB enable constraint CLJOBB
*
ERROR at line 1:
ORA-02437: cannot validate (LDBO.CLJOBB) - primary key violated


SQL> alter table CLJOBB MODIFY CONSTRAINTS CLJOBB ENABLE noVALIDATE;

Table altered.

SQL>


alter table CLJOBB enable VALIDATE primary key;

ERROR at line 1:
ORA-02437: cannot validate (LDBO.CLJOBB) - primary key violated

Cause: attempted to validate a primary key with duplicate values or null values.
Action: remove the duplicates and null values before enabling a primary key.


-----------Primary Key- Firmnumber,Nfinancialyear,Code,Exchange,Bookcode,Dvalidupto,Nproductcode-----


Select A.Firmnumber, A.Nfinancialyear, A.Code, A.Exchange, A.Bookcode, A.Dvalidupto, A.Nproductcode From Cljobb A
Where Rowid > ( Select Min(Rowid) From Cljobb B
Where A.Firmnumber=B.Firmnumber And A.Nfinancialyear=B.Nfinancialyear And A.Code=B.Code And A.Exchange=B.Exchange
and A.Bookcode=b.Bookcode and a.Dvalidupto=b.Dvalidupto and A.Nproductcode=b.Nproductcode
);

Constraint Check

SPOOL C:\CONS.TXT

select 'select '||cc.column_name-
||' from '||c.owner||'.'||c.table_name-
||' a where not exists (select ''x'' from '-
||r.owner||'.'||r.table_name-
||' where '||rc.column_name||' = a.'||cc.column_name||')'
from dba_constraints c,
dba_constraints r,
dba_cons_columns cc,
dba_cons_columns rc
where c.constraint_type = 'R'
and c.owner not in ('SYS','SYSTEM')
and c.r_owner = r.owner
and c.owner = cc.owner
and r.owner = rc.owner
and c.constraint_name = cc.constraint_name
and r.constraint_name = rc.constraint_name
and c.r_constraint_name = r.constraint_name
and cc.position = rc.position
and c.owner = 'LDBO'
and c.table_name = 'CEXIST'
and c.constraint_name = 'CEXISTBOOK';


SPOOL OFF

Constraint stats ORA-02298

Alter table CEXIST DISABLE constraint CEXISTBROKBOOK;

alter table CEXIST disable constraint CEXISTBOOK;
alter table CEXIST ENABLE constraint CK_COMPANYCATEGORY
alter table CEXIST ENABLE constraint CEXISTPRIMARY ;
alter table CEXIST ENABLE constraint COMPANYEXIST ;




SQL> alter table CEXIST ENABLE constraint CEXISTBOOK ;
alter table CEXIST ENABLE constraint CEXISTBOOK
*
ERROR at line 1:
ORA-02298: cannot validate (LDBO.CEXISTBOOK) - parent keys not found


alter table CEXIST MODIFY CONSTRAINTS CEXISTBOOK ENABLE VALIDATE;

ERROR at line 1:
ORA-02298: cannot validate (LDBO.CEXISTBOOK) - parent keys not found



alter table CEXIST MODIFY CONSTRAINTS CEXISTBOOK ENABLE NOVALIDATE;



Constraint stats
Here are the four type of constraint stats. These four constraint stats are applicable for all type of constraints(primary key, foreign key, check etc).

1. ENABLE VALIDATE
2. ENABLE NOVALIDATE
3. DISABLE VALIDATE
4. DISABLE NOVALIDATE


ENABLE VALIDATE is same as ENABLE. Constraint validate the data as soon as we entered in the table.


ENABLE NOVALIDATE is not same as ENABLE. Constraint validates the new data or modified data. It would not validate the existing data in table.


DISABLE NOVALIDATE is the same as DISABLE. The constraint is not checked so data may violate the constraint.


DISABLE VALIDATE means the constraint is not checked but disallows any modification of the constrained columns.



Note : Couple of things needs to be noted down here.

1. Converting NOVALIDATE constraint to VALIDATE would take longer time, depends on how big the data in the table. Although conversion in the other direction is not an issue

2. Disabling primary key constraint will drop the index associated with primary key. Again, when we enable the primary key constraint, it will create the index on the primary key column.

What is the ideal place to use ENABLE NOVALIDATE option?

In a busy environment, some one disabled the constraint accidently or intentionally, and we have already bad data in that table. Now business requested you to load the new set of data, but business wanted to make sure that new set of data should be validated during the load. At this circumstances, we can use ENABLE NOVALIDATE option. This option will validate the new data and old data will not be validated.

What is the ideal place to use DISABLE VALIDATE option?

We disabled the constraint for some reason. We do not want to load any data until we fix the issue and enable the constraint. We can use DISABLE VALIDATE option here. This option would not let you load any data when the constraint is disabled.