delete subject with commands sql

Question

i have code for database for subject and commands table

i want sql statement if i am want to delete subject directly delete all commands for this subject. my database is

create table if not exists subject(
S_Id INT(100) not null AUTO_INCREMENT,
FirstName varchar(255) not null,
title text not null,
PRIMARY KEY (S_Id)
);

create table if not exists comm(
C_Id int not null AUTO_INCREMENT,
message text not null,
S_Id int not null,
PRIMARY KEY (C_Id),
FOREIGN KEY (S_Id) REFERENCES Persons(S_Id)
);

thanks all ...

you must read about triggers. see http://dev.mysql.com/doc/refman/5.0/en/triggers.html — Satish Sharma, Feb 05 '14 at 05:32
Is the foreign key in comm supposed to reference s_id in the subject table, or am I missing the link between the tables? — Joachim Isaksson, Feb 05 '14 at 05:35

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

@Satish Sharma is right here is a link to triggers SQL Server ON DELETE Trigger
for your example

    CREATE TRIGGER sampleTrigger
        ON subject
        FOR DELETE
    AS
        DELETE FROM comm
        WHERE S_Id IN(SELECT deleted.S_Id FROM deleted)
    GO

edited May 23 '17 at 12:21

Community

1
1

answered Feb 05 '14 at 05:36

clancer

613
4
10

i take your comment and paste it to mysql i get error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax – user3264926 Feb 05 '14 at 05:48

score 0 · Accepted Answer · answered Feb 05 '14 at 05:47

If you're using InnoDB, you can use the foreign key contraint ON DELETE CASCADE on your referencing foreign key in comm. (I'm assuming here that the S_Id foreign key in comm is supposed to reference subject, not Persons);

create table if not exists comm(
  C_Id int not null AUTO_INCREMENT,
  message text not null,
  S_Id int not null,
  PRIMARY KEY (C_Id),
  FOREIGN KEY (S_Id) REFERENCES subject(S_Id) ON DELETE CASCADE
);

When this constraint is added, any delete from Subject will delete the corresponding lines in comm automatically.

An SQLfiddle to test with.

As a side note, personally I'm very careful about adding constraints like this that may alter data unexpectedly. If you have cascades set up in multiple steps, a single accidental delete may end up removing a lot of referencing rows.

delete subject with commands sql

2 Answers2