MySQL workbench error

Databases
1

Hi I’m trying to set a foreign key to link tables within MySQL workbench and cannot work out why I’m getting the folowing error.

SQL Error 1005: Can’t create table ‘feedback.#sql-174_8’ (errno: 150)

The SQL I’m trying to run is

[COLOR=“red”]ALTER TABLE feedback.question_answers

ADD CONSTRAINT relation

FOREIGN KEY (relation )

REFERENCES feedback.user (relation )

ON DELETE SET NULL

ON UPDATE CASCADE

, ADD INDEX relation (relation ASC) ;[/COLOR]

The DB is called ‘feedback’ and I have tables called ‘user’ and ‘question_answers’ Both of the relation fields are set as (VARCHAR45) and I cannot get this link to function.

Any ideas?

2

CREATE TABLE user (
iduser int(11) NOT NULL AUTO_INCREMENT,
firstname varchar(255) NOT NULL,
surname varchar(255) NOT NULL,
relation varchar(45) NOT NULL,
PRIMARY KEY (iduser)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

‘CREATE TABLE question_answers (
idquestion_answers int(11) NOT NULL AUTO_INCREMENT,
idquestion int(11) NOT NULL,
relation varchar(45) NOT NULL,
answer varchar(255) NOT NULL,
PRIMARY KEY (idquestion_answers),
KEY idquestion (idquestion),
CONSTRAINT idquestion FOREIGN KEY (idquestion) REFERENCES question_text (idquestion) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8’

3

ALTER TABLE user ADD UNIQUE (relation)

this allows relation to be the target of the REFERENCES in question_answers

:slight_smile:

4

could you do a SHOW CREATE TABLE for both tables please