Why does this not work - Create tables

[ZOR]

Trying to modify this which works:

CREATE TABLE customer
(
customer_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
PRIMARY KEY (customer_id)
) TYPE = INNODB;

CREATE TABLE customer_sales
(
transaction_id INT NOT NULL AUTO_INCREMENT,
amount INT,
customer_id INT NOT NULL,
PRIMARY KEY(transaction_id),
INDEX (customer_id),
FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
) TYPE = INNODB;

Into this which fails. I thought maybe Transaction_ID was a reserved recognised syntax, but replacing OrderID with transaction_ID still no good.

CREATE TABLE company
(
CompanyID INT NOT NULL AUTO_INCREMENT,
CompanyName VARCHAR(30),
PRIMARY KEY (CompanyID)
) TYPE = INNODB;


CREATE TABLE orders
(
OrderID INT NOT NULL AUTO_INCREMENT,
OrderNumber INT,
CompanyID INT NOT NULL,
PRIMARY KEY(OrderID),
INDEX (CompanyID),
FOREIGN KEY (CompanyID) REFERENCES customer (CompanyID)
) TYPE = INNODB;

I was then thinking to add a third table on using foreign key OrderID, but progress shot down in flames. Regards

 

[towerbase]

How are you entering this SQL? PHP?, MySQL Query Brower? DOS command line?

How do you know it fails? What error message do you get?

Andrew
Hampshire, UK

 

[ZOR]

Thanks, I am entering it in the SQL window of phpMyAdmin. Reports unable to create table, I think its the second table where problems are, as the first table gets created. At one point it referenced the line OrderID but cannot get it to do it again. I will keep trying it to get an error number or something. I was hoping the error in syntax would be spotted.

 

[ZOR]

Is spoke to me this time

Error
SQL query:

CREATE TABLE orders(

OrderID INT NOT NULL AUTO_INCREMENT ,
OrderNumber INT,
CompanyID INT NOT NULL ,
PRIMARY KEY ( OrderID ) ,
INDEX ( CompanyID ) ,
FOREIGN KEY ( CompanyID ) REFERENCES customer( CompanyID )
) TYPE = INNODB;

MySQL said:

#1005 - Can't create table '.\mydatabase\orders.frm' (errno: 150)

Hope this throws more light on the problem

 

[ZOR]

Stupid me, its customer instead of company!! Must be because England's out of the cup and drinks not worn off. I was staring at it all the time. Many thanks for comming in.