Question regarding RIs

Database

Question regarding RIs

Hi all

I have been trying to do some test cases and see that when a normal FK is created, we get a _0 table name as well. I have reviewed other discussions on the forum related to this topic and came to know that this table is created when the chck is performed by database and is called a Hard RI.

While using 'with no chck option' (soft RI) doesn't create the _0 table, I was curious to know why does using 'with check option' doesnt create _0 table. As evident from my test cases below, with check option does return error but the error code is different.

error 2700 in case of hard RI and 3513 in case of with check option

Is using with check option same as enforcing a hard RI?

Any help in making me understand this behaviour would be great.

test cases:

Failure 3513 RI violation

Failure 2700 Referential constraint violation: invalid Foreign Key value.

ct t1(x1 int not null,x2 int) primary index(x1);

alter table t1 add primary key(x1);

ins into t1(1,2);

ins into t1(2,3);

ct t2(y1 int, y2 int);

alter table t2 add foreign key(y1) references t1(x1);

help database test;

 *** Help information returned. 3 rows.

 *** Total elapsed time was 1 second.

Table/View/Macro name          Kind Comment

------------------------------ ---- ---------------------------------------

t1                             T    ?

t2                             T    ?

T2_0                           T    ?

ins into t2(4,5);

 *** Failure 2700 Referential constraint violation: invalid Foreign Key value.

drop table t2;

ct t2(y1 int, y2 int);

alter table t2 add foreign key(y1) references with check option t1(x1);

ins into t2(5,4);

 *** Failure 3513 RI violation.

2 REPLIES
Teradata Employee

Re: Question regarding RIs

How can we find RI constraints in the DBC dictionary with the NO CHECK option

N/A

Re: Question regarding RIs

There's no way to get the different types of RI from the system tables, only SHOW will return it.

Regarding the original post:

WITH CHECK OPTION checks using EXISTS/NOT EXISTS on a statement level, you can see this in Explain

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1184_111A/Crea...