use Perl

• NULLs (Score:2)

  by Theory (10)

  I can't put the cancel or finish status in the tasks table because since they will be NULL to start with, I can't put a foreign key constraint from, say, the task.cancel_user_id column to the users.id column since foreign key constraints typically don't allow null values.

  While current relational theory is pretty contentious when it comes to NULL, the fact is that most databases that I'm aware of, including PostgreSQL, MySQL, and SQLite, allow foreign key columns to be NULL.

  —Theory
  • Re:NULLs (Score:2)

    by Ovid (2709) on 2006.07.03 11:36 (#48516) Homepage Journal

    I was doing this in MySQL and having NULL values in the FK column caused MySQL to die a horrible death. And then just to prove my point, I created a small test case in MySQL and it didn't die. There must have been something else going on, but now I can't reproduce it. Damn.

    Reply to This

    Parent

    • Re:NULLs (Score:1)

      by Aristotle (5147)

      I was thinking the same as n1vux all along while reading the post – there just isn’t any need for so many separate join tables, one is perfectly sufficient given a start/cancel/finish enum column. And I’d still do it with such a join table even if NULL FKs work: for one thing, such an enum column for would be naturally extendable without having to touch the schema. For another, you don’t ever need to UPDATE any rows – apart from better scalability, having immutable rows tends t