All Forums Database
mmatten 17 posts Joined 06/07
25 Sep 2012
Strange(?) error creating FK constraints

I'm using Teradata v13.10.
ALTER TABLE t1 ADD CONSTRAINT R1 FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS (PRODUCT_ID);
This statement fails:
ALTER TABLE Failed. 3796: Incorrect referential contraint definition.
But,
ALTER TABLE t1 ADD CONSTRAINT R1 FOREIGN KEY WITH CHECK OPTION (PRODUCT_ID) REFERENCES PRODUCTS (PRODUCT_ID);
This statement succeeds, as does
ALTER TABLE t1 ADD CONSTRAINT R1 FOREIGN KEY WITH NO CHECK OPTION (PRODUCT_ID) REFERENCES PRODUCTS (PRODUCT_ID);
What could be the issue?
Thanks,
Mark
 
 

mmatten 17 posts Joined 06/07
26 Sep 2012

I've seen a couple of posts on here that suggest that WITH CHECK OPTION is not just explicitly stating the default behaviour and that the SQL manual explains this.
There's nothing useful in this one: -
http://tunweb.teradata.ws/tunstudent/TeradataUserManuals/SQL_Reference_--_Data_Definition_Syntax_Example.pdf
 
Could someone please point me at an explaination?
Thanks,
Mark

Qaisar Kiani 337 posts Joined 11/05
26 Sep 2012

I am not sure why you are getting the error while adding the FK constraints. I tried the same on TD 12 & 13.00.01, and haven't faced any issue on both empty or populated tables.
I guess you need to share the table definitions to understand the scenario...

pawan0608 101 posts Joined 12/07
26 Sep 2012

In the first syntax, you are trying to add "Standard Referential Integrity Constraint" and compression is not supported in this. Just Check if any of the column(s) in Parent Key / Foreign Key have any compression applied.
 
You can specify MVC  in Batch Referential Integrity Constraint (Syntax #2)  and Soft Referential Integrity Constraint (Syntax #3)
 

mmatten 17 posts Joined 06/07
26 Sep 2012

Another one for you guys - do you know which DBC view column I can see the standard/batch/with/without option for the constraints?
 
Thanks,
Mark

pawan0608 101 posts Joined 12/07
27 Sep 2012

 I have explored several dbc tables/view, but couldn't find any table/column which store this information

 
 

mmatten 17 posts Joined 06/07
27 Sep 2012

Me too. I can't even find a reference (!) to it in the Teradata docs :(

macktd 23 posts Joined 09/12
27 Sep 2012

Hey bro' check with the following query, u may get
DBC.All_RI_Parents
DBC.All_RI_Children

macktd 23 posts Joined 09/12
27 Sep 2012

The WITH CHECK OPTION option is used to specify that Referential Integrity (RI) is only checked at the end of a batch. The NO option indicates that RI is not enforced.

You must sign in to leave a comment.