Teradata Foreign Key side effects

Database
Fan

Teradata Foreign Key side effects

Hi all

I've noticed something strange in my schema when adding a FK to an existing table lets say "mytable" a table called "mytable_0" is created. My first question is what is this table and does it have to be there , if not how can I get rid of it.

Now lets say I drop the FK (the table remains) and rename the table "mytable_old" and create a new "mytable" and try add recreate the old FK I get this "error table 'TESTDB.mytable_0' already exists.

Am I missing something here ? Shouldn't TD manage this stuff internally ?

Any help would be much appreciated I'm Using TD 12
3 REPLIES
Enthusiast

Re: Teradata Foreign Key side effects

This is a table for tracking foreign key violations. It exists because you defined "Hard RI", or database-enforced RI. Using Soft RI (WITH NO CHECK OPTION) in your FK declaration will inform the optimizer that a relationship exists while not creating any additional tables ore having the database perform the RI checks. If you use Soft RI, you'll have to make sure that your ETL maintains proper referential integrity.
Fan

Re: Teradata Foreign Key side effects

Strangely adding WITH CHECK OPTION also seems to have fixed this issue
Fan

Re: Teradata Foreign Key side effects

Same issue - http://forums.teradata.com/forum/database/is-god-is-creating-these-new-tables