Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
I suggest you stop naming all your primary keys as id and name them with "full" names, e.g. job_id, jobtuning_id, etc. Then you can use the syntax jobtuning JOIN jobstep USING (jobtuning_id) which will make it harder for you to write senseless joins. Even if you keep working with the ON syntax, most of the joins will have same column name in the two sides which is easy to get used to and check. –  ypercube Oct 18 '13 at 19:48
1  
The only DBMS I know where something like that is possible is DB2. You can define your own data types as "distinct". If you use a different ype for the job_id, jobtunig_id and jobstep_id DB2 would not let you compare a job_id against a jobstep_id. But unforunately Postgres type system won't let you do something similar. –  a_horse_with_no_name Oct 18 '13 at 19:50
    
Great naming tip! Unfortunately we're Django which has it's own habits (including prefixing the application name to each table). –  eradman Oct 18 '13 at 19:56

1 Answer 1

No. It is up to the developer/DBA to right senseful joins, in this regard PostgreSQL is just a slave who does what its master commands.

In any case, defining the boundary between joins which makes sense and those don't is not trivial. If that column is there because of a denormalization, for example, then it perfectly makes sense in spite of a nonexistent foreign key. You can always decrease the possibility for accidental misjoins by not naming the primary key column just id but jobstep_id, for example.

Also, when should it emit the warning? When you hit it with a 1000 line SQL script, containing a hundred statements, out of which 20 contain 'inconsistent' joins?

All in all, this is rather a code review problem than anything else.

share|improve this answer
    
Agreed, developers needs to write sensible joins. I'm in the habit of giving example quiries to coworkers who don't write much SQL, and it appeared that some mistakes might be avoided with a --warn flag. Surely good naming conventions would also help. –  eradman Oct 18 '13 at 20:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.