CODEWITHSUNDEEP
oracle-database
Srinivasan
Srinivasan

Reputation: 12040

how to set foreign key constraint for new data

I have one table(T1) and have some records on that table. I want to create foreign key constraint for one column(c1) but I want to ignore constraint for existing records(old records). I mean FK constraint should applicable for new records.

Is it possible?

Upvotes: 1

Views: 595

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

You can create constraints in Oracle which ignore any pre-existing bad data but are enforced for any inserts or updates, using the NOVALIDATE keyword, e.g.:

create table t1 (id number primary key);

create table t2 (id2 number primary key, id number not null);

insert into t2 values (1,1);

alter table t2 add constraint t2_fk
  foreign key (id)
  references t1 (id)
  novalidate;

insert into t2 values (2,2);

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

The only downside is that the constraint cannot be used by the Oracle optimizer in making its decisions. For this reason it's preferable to fix the existing data if at all possible, before adding a validated constraint.

Upvotes: 1

eliatou
eliatou

Reputation: 744

The solution of SebastianH is a good one. Another solution if you can't create table is to do the job with a trigger. The problem of this solution is the delete rows question of the leading table. It could be deal with another trigger.

Upvotes: 1

Related Questions