CODEWITHSUNDEEP
mysqlsqlsql-server
angel
angel

Reputation: 4632

how do you delete a row, if this has foreign keys? sql

for example if you have this rows

create table table1 
(
column1 int primary key,
column2 varchar(50),
column3 varchar(50),
column4 varchar(50),
)

create table tabla2
(
col1 int primary key,
col2 int,
col3 varchar(50),
foreign key(col2) references table1(column1)
)

and for example i have a row 

insert into table1(column1,column2,column3,column4) values (1,'a','b','c');

insert into table2(col1,col2,col3) values (1,1,'xxx');

and i want to delete all these rows (just them)

delete from table1 where colum1=1;

doesn't work,

i know i can first delete the another and after it, but i have a database with a lot of tables and they have a foreign key since another table, and i want to delete the row, for all rows are relationed with this, delete too

Upvotes: 0

Views: 114

Answers (2)

zerkms
zerkms

Reputation: 254906

foreign key(col2) references table1(column1) on delete cascade

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Upvotes: 3

dee-see
dee-see

Reputation: 24078

Add ON DELETE CASCADE to your foreign key constraint.

Upvotes: 1

Related Questions