MySQL syntax error - can't create a table

Question

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 ?

user330315 · Accepted Answer · 2013-11-04 23:14:24Z

1

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;

answered Nov 4, 2013 at 23:14

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Newbie Over a year ago

Thanks, but why can't I just refer to any column ? why can't set FK to 'method' not 'method_id' ?

user330315 Over a year ago

@Newbie: a foreign key can only reference a primary key (or unique constraint). Think about it: if there were multiple rows with the same value in the "target" column, which one should be the target of the referencing row?

Newbie Over a year ago

I got the idea , I'm still new to php/mysql .. setting "method" to be a unique column also resolved my problem , Thanks

Collectives™ on Stack Overflow

MySQL syntax error - can't create a table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related