Member Avatar for Fenerbahce

Hi,

I can create all tables but order table. What is wrong, is it foreign key?

CREATE TABLE customer(
customerId INT(10) NOT NULL AUTO_INCREMENT, 
customerName VARCHAR(10),
customerSurname VARCHAR(10),
customerAddress VARCHAR(10),
PRIMARY KEY (customerId));

CREATE TABLE order(
orderId INT(10) NOT NULL AUTO_INCREMENT, 
customerId INT(10),
PRIMARY KEY (orderId),
FOREIGN KEY (customerId) REFERENCES customer(customerId));

CREATE TABLE orderDetail(
orderDate DATE,
orderId INT(10), 
productId INT(10),
FOREIGN KEY (orderId) REFERENCES order(orderId),
FOREIGN KEY (productId) REFERENCES product(productId));

CREATE TABLE product(
productId INT(10) NOT NULL AUTO_INCREMENT, 
productName VARCHAR(10),
PRIMARY KEY (productId));

CREATE TABLE stock(
stock INT(10), 
productId INT(10),
FOREIGN KEY (productId) REFERENCES product(productId));

Thanks

  • 2 Contributors
  • 2 Replies
  • 114 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Fenerbahce

Dani AI

Generated

Good catch by and glad to see got past the immediate blocker. A few practical points that commonly cause foreign-key errors in MySQL and that are worth checking even after the CREATE succeeds:

Foreign-key support requires the right storage engine. Only engines such as InnoDB enforce FKs; MyISAM silently ignores them. Confirm the engine and, if needed, convert a table before adding FKs:

SHOW CREATE TABLE your_table;
ALTER TABLE your_table ENGINE=InnoDB;

Column definitions must match exactly. The referenced column needs to be indexed (PRIMARY or UNIQUE), and both sides should have identical data types and attributes (signed vs unsigned, length/charset for strings). If they differ, FK creation will fail.

Creation order matters. Parent tables must exist before child tables that reference them. If you build many tables in one script, create all referenced tables first or add FK constraints with ALTER TABLE after both tables exist.

Debugging tips and best practices: read the exact error message (or run SHOW ENGINE INNODB STATUS for InnoDB-specific details), name your FK constraints explicitly so error messages are clearer, and consider specifying ON DELETE/ON UPDATE actions (CASCADE/SET NULL/RESTRICT) to match intended behavior. Keep sensible column sizes and avoid generic tiny VARCHAR limits for names/addresses.

For authoritative rules and edge cases, see the MySQL manual on foreign-key constraints: .

Recommended Answers

All 2 Replies

Member Avatar for blater

"order" is a reserved word so you need to quote it if you're going to use it for a table name. See here for details.

Member Avatar for Fenerbahce

thanks, it works with "orders".

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.