CODEWITHSUNDEEP
sqlmysqlforeign-keysmyisamcascading-deletes
dko
dko

Reputation: 894

Problem creating foreign keys in mySql

I created a foreign key in my sql by the following statemnt..

ALTER TABLE `users` ADD FOREIGN KEY ( `id`) 
REFERENCES `user_login` (`user_id`) 
ON DELETE CASCADE ;

The creation appears to succeed then after that I execute a delete statement

DELETE From user_login WHERE user_id = 1576;

yet in users the row still exists that is referencing that. I open up the mysql workbench and it doesn't show any signs that the foreign key was created. Does anyone know why this would be? Or what I am doing wrong? It is a one-to-one relationship in the two tables.

Upvotes: 1

Views: 264

Answers (3)

Atishay
Atishay

Reputation: 31

Copy and paste this code in your Mysql script editor and run. You will have two tables categories and products these tables having cat_id as foreign key.

CREATE DATABASE IF NOT EXISTS dbdemo;

USE dbdemo;

CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;

CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

Upvotes: 0

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99921

The table may be in MyISAM format, which does not support foreign keys.

Try converting it to InnoDB first:

alter table users engine=InnoDB;

Upvotes: 2

user585756
user585756

Reputation:

You have to also make sure that both users.id and user_login.user_id have an index each.

Upvotes: 1

Related Questions