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
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.