CODEWITHSUNDEEP
mysqlpostgresql
magicapples
magicapples

Reputation: 51

MYSQL create table, constraint, foreign key

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
);

Upvotes: 3

Views: 1088

Answers (1)

magicapples
magicapples

Reputation: 51

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
);

Upvotes: 2

Related Questions