RI to be enforced in tables containing data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shsandeep - 25 Jul 2006 14:24 GMT

I created all the tables in my database (about 100 of them) a while ago
without enforcing the RI constraints.
Now, most of them are loaded with data and I need to execute those RI DDLs
on the entire database.
Is this something completely wrong or ok to go ahead with? I require your
suggestions here.

Thanks.

Cheers,
San.

Reply to this Message

Shashi Mannepalli - 25 Jul 2006 15:46 GMT

You can create the RI at what/when ever you want to.
But if the data is violating those RI rules then you will not be able
to create RI for them.
Then u need to figure out what are the RI violated ROWS and what to do
with them...ETC

cheers...
Shashi Mannepalli

> I created all the tables in my database (about 100 of them) a while ago
> without enforcing the RI constraints.
[quoted text clipped - 7 lines]
> Cheers,
> San.

Reply to this Message

mirof007 - 26 Jul 2006 04:31 GMT

> You can create the RI at what/when ever you want to.
> But if the data is violating those RI rules then you will not be able
[quoted text clipped - 4 lines]
> cheers...
> Shashi Mannepalli

Actually, in a case where you expect some of your data to viloate the
constraint, you can simply put the table into the set intergity pending
state (using the SET INTEGRITY OFF statement) prior to enabling the
enforcement of the constraint, and then subsequenlty run SET INTEGRITY
IMMEDIATE CHECKED with an exception table, that will delete all
violating rows for you and move them to the exception table.This works
for RI constraints, as well as check constraints, BTW.

Regards,
Miro

Reply to this Message