On foreign key syntax

In a DataJoint table definition, the foreign key

-> experiment.Session

expresses the constraint that an entity of the current class shall never appear without a matching entity of class experiment.Session.
This definition also brings in the primary key attributes from experiment.Session into the current class definition if they are not yet part of the current class. We do not even need to know what the primary key of experiment.Session to impose the constraint.

In SQL, the same constraint has the following form:

animal_id INT NOT NULL,

session_id SMALLINT NOT NULL,

CONSTRAINT FOREIGN KEY (animal_id, session_id)

     REFERENCES experiment.Session (animal_id, session_id)

The SQL variant specifies several extra steps and extra pieces of information:

First, we must declare the attributes animal_id and session_id to be used for referencing the primary key attributes of experiment.Session. They must match the data types of the primary key attributes to experiment.Session. It is not helpful that SQL syntax allows defining a foreign key between attributes on incompatible datatypes. Although it is possible to declare foreign keys that reference other unique keys of the referenced table besides the primary key, it is uncommon and usually a bad idea. Some database engines even allow referencing sets of attributes that do not constitute a unique key, which leads to nonsense.

Second, the foreign key constraint specifies the names of the primary key attributes of experiment.Session. Again, since the most common and valid way to reference entities is by their primary key, this syntax is redundant.

Finally, the foreign key constraint specifies the names of the foreign key attributes of the referencing class. In DataJoint, we usually keep the same name as the primary key attribute of the referenced class, so this information becomes redundant also.

In case we do want to rename the foreign key attributes in DataJoint, we can use the following syntax

(subject, session) -> experiment.Session(animal_id, session_id)