MYSQL create table, constraint, foreign key

Question

I am following along with a UDEMY course about SQL. The instructor is working in postgresql. I am working in MySQL workbench. Our corporate development is in MySQL 5.6 on the AWS platform. So I want to learn in MySQL.

There is a lecture about "creating tables" and using constraint and foreign key commands to link the primary keys in two different tables. This is something I want to do with my data, so the challenge is relevant. The instructor's code does not compile for me. I get "error code 1215. Cannot add foreign key constraint".

I have read here that the variables must have the exact same definition. So I changed the instructor's tables from "serial" to int. I am still getting the error. I have tried simplifying the code. I'm still not getting anywhere. Can anybody help me understand why it is not executing?

create table if not exists accounts(

user_id INT auto_increment not null,
username varchar(50) unique not null,
password varchar(50) not null,
email varchar(350) unique not null,
created_on timestamp not null,
last_login timestamp,
primary key (user_id)
);


create table if not exists role(
role_id INT auto_increment not null, 
role_name varchar(250) unique not null,
PRIMARY KEY (role_id)
);

create table accounts_role(
user_id INT,
role_id INT,
grant_date timestamp,
primary key (user_id, role_id),

constraint accounts_role_role_id_fkey foreign key (role_id)
    references role(role_id) match simple
    on update no action
    on delete no action,

constraint accounts_role_user_id_fkey foreign key (user_id)
    references account (user_id) MATCH SIMPLE
    on update no action
    on delete no action
);

Could you please edit your post, select all the sql code and press the {} button above the text box to format it as code. I'd do it for you but this button doesn't show up on an iPhone :( — Caius Jard, Nov 18 '17 at 05:57
I mostly work with other brands of database but your syntax for adding the foreign key looks like it's missing some keywords. To add an fk to an existing table you need to issue a query looking more like ALTER TABLE blah ADD CONSTRAINT constraintname FOREIGN KEY blah blah - see https://stackoverflow.com/questions/10028214/add-foreign-key-to-existing-table or similar. Basically, try adding `ALTER TABLE blah ADD ` before the word CONSTRAINT in your code. Make sure columns referenced in foreign keys are indexed — Caius Jard, Nov 18 '17 at 06:02

score 2 · Answer 1 · answered Nov 18 '17 at 06:17

good grief.
Thanks Caius, i was implementing your suggestion and I found the answer to my problem.

i realized that my table is named "accounts" and i was creating a constraint on "account" ...

create table accounts_role(
user_id INT,
role_id INT,
grant_date timestamp,
primary key (user_id, role_id),

constraint accounts_role_role_id_fkey foreign key (role_id)
    references role(role_id) match simple
    on update no action
    on delete no action,

constraint accounts_role_user_id_fkey foreign key (user_id)
    references accounts(user_id) MATCH SIMPLE
    on update no action
    on delete no action
);

MYSQL create table, constraint, foreign key

1 Answers1