Mysql cross database foreign key

Question

I am trying to create a foreign key between ticket table of task_flow database with user table of sardia database:

user_id int(10) UNSIGNED No
Id int(10) UNSIGNED No

I created the query

ALTER TABLE ticket
ADD CONSTRAINT fk_u_id
FOREIGN KEY (user_id) REFERENCES sardia.user(Id);

"Foreign key constraint is incorrectly formed" Can somebody help how I can achieve this?

The definition looks correct to me. Perhaps you have bad characters in the code. — Gordon Linoff, May 29 '18 at 14:12
@GordonLinoff the full error is Can't create table `task_flow` Foreign key.. why does it say cant create.. database name? — RandomUser, May 29 '18 at 14:14
To all those who might have the same problem,I had forgoten to set the users id column as primary key.Problem solved! — RandomUser, May 29 '18 at 14:29

score 1 · Accepted Answer · answered May 29 '18 at 14:14

1

Someone has already ask something related to it, click here to see it.

Try something like this:

ALTER TABLE ticket ADD grade_id SMALLINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE ticket ADD CONSTRAINT fk_u_id FOREIGN KEY (user_id) REFERENCES sardia.user(Id);

Hope it helped you.

answered May 29 '18 at 14:14

Daniel Castro

36
5

that's exactly what I am doing – RandomUser May 29 '18 at 14:15

Mysql cross database foreign key

1 Answers1