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.
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?
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: -
Could someone please point me at an explaination?
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...
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)
Another one for you guys - do you know which DBC view column I can see the standard/batch/with/without option for the 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.