Strange(?) error creating FK constraints

Database
Enthusiast

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

 

8 REPLIES
Enthusiast

Re: Strange(?) error creating FK constraints

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

Could someone please point me at an explaination?

Thanks,

Mark

Enthusiast

Re: Strange(?) error creating FK constraints

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

Enthusiast

Re: Strange(?) error creating FK constraints

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)

Enthusiast

Re: Strange(?) error creating FK constraints

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

Enthusiast

Re: Strange(?) error creating FK constraints

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

 

Enthusiast

Re: Strange(?) error creating FK constraints

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

Enthusiast

Re: Strange(?) error creating FK constraints

Hey bro' check with the following query, u may get

DBC.All_RI_Parents

DBC.All_RI_Children

Enthusiast

Re: Strange(?) error creating FK constraints

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.