Creating a relation between tables programatically in SQL can be done but there are items to keep in mind as listed below:

• The relation of the field's table to connect to must be a primary key.


• The inspector of the relation has a "Consistency" option as oppose to "Deletion Control".
  
  

When manually creating a relation with 4D, child records are handled by the "Delection Control" where there are 3 options at the time the parent record is deleted. As for a relation created by SQL, the "Consistency" setting works for both deleting and updating of parent and child records. In "Cascade" mode, parent records that are deleted or updated will also affect the child records. While in "Restricted" mode a parent record can be updated (execept for the reference field to the child) but cannot be deleted if there are child records referenced to it.


• Multiple relations cannot be created programatically of the same field.
  

Sample code to programatically create a relation:

Begin SQL    ALTER TABLE Table_1    ADD CONSTRAINT relation_t1_t2    FOREIGN KEY(ID)REFERENCES Table_2(ID); End SQL

The connection made in the Structure:




See Also:

• Tech Tip #75242 Interpreting the Structure Editor: Many-To-One or Primary Key-Foreign Key?


• Relation properties when manually created.


• SQL: foreign key with cascade and restrict definition.