Simple Constraint Creation

[aahan]

I need to create a constraint on a table T1 with columns of interest as ColA and ColB, with a constraint as under :

The Value I insert into ColA should exist on ColB. I cannot create a trigger since it will result into a mutating table, the moment I try to do a select on the table I am residing the trigger on.

I tried this :

ALTER TABLE T1 ADD CONSTRAINT colA_fk FOREIGN KEY (colA) REFERENCES T1(ColB)

However for this ColB must be a PK or unique, which is not the case.

What is a better way of achieving this ( maybe check constraint) ???

Thanks.

 

[sem]

I suppose that only trigger may help. Mutating table is not a problem, but rather inconvenience (I mean it's solvable).

Regards, Dima

 

[aahan]

Can you be more explicit in explaining how I go around doing it. I am not familiar with the work around for mutating table ?

 

[sem]

Just go to google and enter

oracle mutating table

instead of opening this 1000th thread on this account.

Regards, Dima

 

[aahan]

Thanks sem. Made the Trigger an Autonomous Transaction and it did the trick.

Thanks Again.

 

[sem]

Be very careful: while it's the simples solution it's not suitable for some cases: autonomous transaction doesn't see changes made by parent one. If you need to limit the number of rows in table or populate the field with record number, you can not do it this way, because the number of rows being calculated in trigger is fixed during processing. E.g. if your table A contains 999 rows and you need to limit this number by 1000, then insert into A select * from A will create those rows without any error. So, my conclusion is that the good old way of storing changes in package variable still is the best.

Regards, Dima