MySQL Format

[Twinkie]

Why does this query fail?

CREATE TABLE Order_Items (
Order_ID CHAR(72) NOT NULL FOREIGN KEY REFERENCES Orders(ID),
Product_ID INT(10) UNSIGNED NOT NULL FOREIGN KEY REFERENCES Items(ID),
Quantity INT(10) UNSIGNED NOT NULL,
Notes VARCHAR(255)
);

I know that the 'correct' form is with the constraints defined separately,

CREATE TABLE Order_Items (
Order_ID CHAR(72) NOT NULL,
Product_ID INT(10) UNSIGNED NOT NULL,
Quantity INT(10) UNSIGNED NOT NULL,
Notes VARCHAR(255),
FOREIGN KEY (Order_ID) REFERENCES Orders(ID),
FOREIGN KEY (Product_ID) REFERENCES Items(ID)
);

but why then can you define primary keys in the column definition? I prefer the SQL / Oracle / Microsoft SQL syntax, and hope to be able to use it with little changes to MySQL.

[blobtech]

You are probably using the default MyISAM engine, which does not support foreign keys.
Both tables should be of type InnoDB.

Use the code:

CREATE TABLE `...` ( ... ) ENGINE = INNODB;

Or

ALTER TABLE `...` ENGINE = INNODB;

Please note that you could replace the ENGINE keyword with TYPE, they are aliases.

[misson]

For MyISAM tables, MySQL ignores FOREIGN KEY constraints rather than considering them errors. The real cause is that MySQL just doesn't support that syntax, which is an extension to the SQL standard. Why didn't the MySQL developers add support for both? I'm not sure, but it's probably because there were more important tasks (additional functionality, bug fixes, performance) than adding a little syntactic sugar.

[Twinkie]

I like the way you worded that misson :P

However, thanks for the info about table types, I lost my database design notes a little while ago and neglected to include table type definitions. MySQL seems to have a vastly different syntax than the SQL standard, and that bothers me. Hopefully now that I stopped using w3schools.com, I won't be getting anymore misinformation about it.

[nirajkum]

mysql is light weighted database ... so that expect complicated things from them

[misson]

I lost my database design notes a little while ago and neglected to include table type definitions.

Have you heard the good news? The words are SHOW CREATE TABLE, and they show Bob's love for you.

MySQL seems to have a vastly different syntax than the SQL standard, and that bothers me. Hopefully now that I stopped using w3schools.com, I won't be getting anymore misinformation about it.

Not so different from the standard, which every DBMS deviates from noticeably. For MySQL, I always head over to the MySQL reference manual.

[xav0989]

DBMS engines respect SQL standards less than browsers respect WWW standards.