Hello sir,
I am trying to add a referential constraint, like this:
alter table t1_name add constraint Constraint_name
foreign key (col_name) references t2_name;
BUT GETTING ERROR:
alter table t1_name add constraint Constraint_name
*
ERROR at line 1:
ORA-02298: cannot validate (GANESH.Constraint_name) - parent keys not found
Can you please tell me the solution.
I want to add this constraint.
What could be the problem.
Waiting for your reply sir,
Krishna
Hi
r u trying to actually execute the statement as below or r u giving any name to constraint_name,
"alter table t1_name add constraint Constraint_name
foreign key (col_name) references t2_name;"
constraint_name is a reserved word, in fact a column name in user_constraints.
so u better give some meaningful constraint name....
HTH
Srini
It seems to me this message is self-explaining :
you try to create a FK between a child table and a parent table but in the child table "t1_name" exist records with a column "col_name" that is not present in the parent table "t2_name";
If you want to enable this constraint, you must first create an exception table; this is done as follows (when on unix the source is present in $ORACLE_HOME/rdbms/admin/utlexcpt.sql):
create table exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
When you try to enable the constraint you must add the following
phrase "..exceptions into.exceptions" to the enable statement; the rows that violate the constraint are written to the exception table when you try to enable the constraint.
You must then decide what to do with the violating rows that are still
present in the child table (perhaps delete them, or copy them to another table and then delete them, etc.)
When these violating records are removed from the dependent table, the
constraint can be enabled.
Hi sir,
I was just giving a generic name for this session only.
Ya, the error is self explanatory,
i just did'nt think enough on it.
Thanks for the help,
Krishna