When creating circular referencing tables in Teradata is the following relation possible, or should the database prevent such a relation from being created?

CREATE TABLE tab1(a int not null primary key references tab2(a)); // a forward reference
CREATE TABLE tab2(a int not null primary key references tab1(a)); // a circular reference

Restrictions state that no inserts, updates, deletes or drops can be performed.

Creating tables with this kind of circular relationship is valid, although currently it might not be particularly useful in Teradata. The relationship does enforce the referential integrity as defined by the user, but currently Teradata only supports immediate checking of constraints for each statement as they are executed. Therefore, inserting, updating and deleting when this constraint is in place is not allowed because this would require checking only after multiple statements have been executed. Dropping either table is not allowed since both tables are parent tables. However, note that the ALTER TABLE statement can be used to drop the constraints. Then rows could be inserted, updated and deleted in the two tables, and the tables could be dropped. Also, this circular referential integrity constraint could be added back after doing data maintenance on the tables to again enforce the constraint (that is, every row in tab1 has one and only one matching row in tab2). Once data has been placed in the tables, this relationship might be appropriate for some tables. In the future, by supporting the ANSI SQL capability of deferring the checking of constraints to the end of the transaction, this kind of relationship would become more useful. For instance, inserts into both tables could occur to insert matching rows in a single transaction with the check being made after both have been inserted. In the meantime, the workaround of dropping the constraints, doing the data maintenance and then adding back the constraints must be done for this kind of circular constraint.

In this case, once users realize that rows cannot be inserted, deleted or updated, they would refrain from using this kind of circular referential integrity. Disallowing it seems unnecessary, especially since it is possible that some users will find it useful and, in the future, deferred constraint checking might be supported. If users do create tables with these constraints and decide they want to drop the constraints or drop the tables, the main thing to remember is that the ALTER TABLE statement can do this.