Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    Unanswered: key constraints inline or separate? (was "primitive n00b question")

    I notice that it's uncommon to write a foreign key constraint in-line, like the following, but I'm unclear why... and also why some prefer the primary key constraint to be on a line below the column declaration. Why is this?

    Code:
    CREATE TABLE stands
    (
       standID   INTEGER   NOT NULL   PRIMARY KEY
    ,  slope      INTEGER   NOT NULL   FOREIGN KEY 
    );

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I prefer to see the PRIMARY KEY and FOREIGN KEY declarations on separate lines from the column name and data type. I can explain my reasoning, although I'm sure that others have their own reasons for choosing one style or the other.

    The data type and NULL or NOT NULL are column definitions... They are fundamental to column definitions in SQL and SQL-like languages. The PRIMARY KEY and FOREIGN KEY declarations are constraints, they describe limitations on a table and not all SQL engines support them.

    MySQL is a notorious offender in the area of constraints because it will usually accept the syntax, but may or may not enforce the constraint depending on the MySQL version, the database engine being used, etc.

    -PatP

  3. #3
    Join Date
    Feb 2009
    Posts
    104

    So when reading code

    That makes it easier to read code, too, I would imagine. Thanks.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Another reason is that unique/foreign key constraint definitions cannot be "inline" if you have multi-column keys. So instead of using different ways to write those constraints (sometimes inline, sometimes separately), it is much better to stick to the same style for all table definitions.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    even in the ANSI SQL forum you take a slam at mysql, eh pat?

    it grows tiresome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2009
    Posts
    104

    thanks -- resolved on in-line constraints.

    Quote Originally Posted by stolze
    Another reason is that unique/foreign key constraint definitions cannot be "inline" if you have multi-column keys. So instead of using different ways to write those constraints (sometimes inline, sometimes separately), it is much better to stick to the same style for all table definitions.
    Thanks. This makes clear sense.

  7. #7
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by rbfree
    I notice that it's uncommon to write a foreign key constraint in-line, like the following, but I'm unclear why... and also why some prefer the primary key constraint to be on a line below the column declaration. Why is this?

    Code:
    CREATE TABLE stands
    (
       standID   INTEGER   NOT NULL   PRIMARY KEY
    ,  slope      INTEGER   NOT NULL   FOREIGN KEY 
    );
    ANSI-invalid syntax. (primary key part ok, foreign key part invalid.) A foreign key is a table constraint, not a column constraint.

    The statement above has no referenced table specified for the foreign key. A foreign key must always reference to a table (usually a second table, sometimes itself).

    To verify a SQL statements standard compliance, check out the SQL Validator for an instant syntax verification:
    Mimer SQL Developers - Mimer SQL Validator

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by JarlH
    ANSI-invalid syntax. (primary key part ok, foreign key part invalid.) A foreign key is a table constraint, not a column constraint.
    You are wrong here. The only reason why the example syntax is invalid is because the it would have to be "columnName INT REFERENCES tableName(colName)". Otherwise, you can inline a FOREIGN KEY constraint as shown. (Subclause 11.4, "<column definition>" is ISO/IEC 9075-2003 allows for a <references specification> there.

    p.s: I would argue that a foreign key is a row constraint or a constraint on the table. It only restricts the values allowed in the columns of a single row and is not across rows.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Yes, you're right. It's obvious that I need to check the standard one more time. Thanks!

  10. #10
    Join Date
    Feb 2009
    Posts
    104

    resolved -- thanks

    Thanks for both these entries. Yes, the example was poor. My apologies for that. Lesson learned. I did get the logic, though. Just had to quit for awhile and come back and look... and the answer was obvious and the question seemed silly. Again, thanks for all help and patience with n00b issues.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •