Search code examples
sqlpostgresqlforeign-keyscreate-table

Postgres constraint references

When defining a field, what is the difference between:

,cadastre           integer NOT NULL

,CONSTRAINT fkey_affaire_vl_cadastre FOREIGN KEY(cadastre)
    REFERENCES public.vl_cadastre (obj_id) MATCH SIMPLE
    ON UPDATE RESTRICT ON DELETE RESTRICT

AND directly

,cadastre           integer NOT NULL REFERENCES public.cadastre (obj_id)
Solution

  • Both constructs do create a foreign key. The short expression is called an inline foreign key.

    The main difference is that the long expression allows you to choose the name of the constraint, while the first one doesn't. This is handy if you need to later on manipulate the constraint (say, drop it), since you know its name beforehand.

    In your code, the first example uses options on update restrict and on delete restrict. This is also supported in the inline form of a foreign key declaration.