CODEWITHSUNDEEP
phpmysqlsyntaxsyntax-error
Newbie
Newbie

Reputation: 69

MySQL syntax error - can't create a table

here is my code

CREATE TABLE IF NOT EXISTS items
( 
id INT NOT NULL AUTO_INCREMENT, 
name varchar(256) , 
description TEXT,
price INT , 
images TEXT,
views INT ,
hidden TEXT,
purchases INT,
PRIMARY KEY (id) 
);

CREATE TABLE IF NOT EXISTS methods 
( 
method_id INT NOT NULL AUTO_INCREMENT, 
method varchar(256),
username varchar(256),
password varchar(256),
PRIMARY KEY (method_id)
);

CREATE TABLE IF NOT EXISTS payments
( 
payment_id INT NOT NULL AUTO_INCREMENT, 
item_id INT NOT NULL, 
method varchar(256),
display INT,
PRIMARY KEY (payment_id) ,
FOREIGN KEY (item_id) REFERENCES items (id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (method) REFERENCES methods (method) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=latin1;

The first 2 tables gets generated ok, but the third one "payments" gives me error code 150 ! which should be related to FK ?!

any help ?

Upvotes: 0

Views: 114

Answers (1)

user330315
user330315

Reputation:

The primary key in the methods table is method_id not method. And the data type is INT not VARCHAR(256)

You need:

CREATE TABLE IF NOT EXISTS payments
( 
  payment_id INT NOT NULL AUTO_INCREMENT, 
  item_id INT NOT NULL, 
  method_id int,  -- this is different
  display INT,
  PRIMARY KEY (payment_id) ,
  FOREIGN KEY (item_id) REFERENCES items (id) ON DELETE CASCADE ON UPDATE CASCADE,

  -- and this line is different
  FOREIGN KEY (method_id) REFERENCES methods (method_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Upvotes: 1

Related Questions