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?

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

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

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.

even in the ANSI SQL forum you take a slam at mysql, eh pat?

it grows tiresome

Thanks. This makes clear sense.

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

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.

Yes, you're right. It's obvious that I need to check the standard one more time. 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.