Like most relational databases, PostgreSQL provides foreign keys as a means of providing referential integrity
CREATE TABLE job ( id serial NOT NULL PRIMARY KEY ) CREATE TABLE jobtuning ( id serial NOT NULL PRIMARY KEY, job_id integer REFERENCES job (id) ) CREATE TABLE jobstep ( id serial NOT NULL PRIMARY KEY, jobtuning_id integer REFERENCES jobtuning (id) )
In a case such as this when an intermediate join table is used it becomes very easy to write a query which appears to work, but is wrong:
JOIN jobstep ON jobtuning_id=jobtuning.job_id
It should be:
JOIN jobstep js ON js.jobtuning_id=jobtuning.id
One of the strengths of SQL is the ability to form arbitrary relationships, but is it possible to ask Postgres to issue a warning when a join is written that is inconsistent with the way foreign keys or other constraints are defined?