Cascade on delete sql

Question

Im working on a small website that involves the use of databases, I'm using this two tables to users, in the first table i add the users and his information, and in the second one I add only the user id the course id.

My question is how can I add the cascade on delete, in which table?

CREATE TABLE IF NOT EXISTS `students` (
  `studentid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `lname` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `studentcode` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `courseid` int(11) NOT NULL,
  PRIMARY KEY (`studentid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=27 ;

CREATE TABLE IF NOT EXISTS `idtables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `studentid` char(64) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=43 ;

score 1 · Answer 1 · answered May 29 '14 at 19:35

1

You need to create a FORIEGN KEY referencing to students table studentid with ON DELETE CASCADE option like below

CREATE TABLE IF NOT EXISTS `idtables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `studentid` char(64) COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`id`),
   CONSTRAINT FK1 FORIEGN KEY(studentid) REFERENCES students(studentid) <-- Here
   ON DELETE CASCADE <-- HERE
)

answered May 29 '14 at 19:35

Rahul

73,987
13
62
116

It is the same if I use studentid or courseid, to do the cascade delete ? – Carlos Perez May 29 '14 at 19:39
@CarlosPerez, NO you can't reference to `courseid` since that's not defined as primary key in student table. – Rahul May 29 '14 at 19:40
@Sammitch, you can try, it works as well fine. – Rahul May 29 '14 at 19:42
@Rahul ooh I see that, so to do the delete on cascade I have to use the primary – Carlos Perez May 29 '14 at 19:42
@CarlosPerez, that's right, I mean if you are creating a FK constraint, it should point to PK in other table. Moreover, in your student table studentid is of type int(11) then why in your idtables you declare studentid as char(64) .. declare it as int(11) as well. – Rahul May 29 '14 at 19:46
good catch, i will change that – Carlos Perez May 29 '14 at 19:48

score 1 · Accepted Answer · answered May 29 '14 at 19:36

1

You need to define this behaviour in a FOREIGN KEY constraint like:

CREATE TABLE IF NOT EXISTS `idtables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseid` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `studentid` char(64) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY fk_students_id (studentid)
    REFERENCES students (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=43 ;

answered May 29 '14 at 19:36

Sammitch

27,459
7
46
75

what the fk_students_id means ? – Carlos Perez May 29 '14 at 19:38
@CarlosPerez, it's the constraint name. – Rahul May 29 '14 at 19:39
@Sammitch awesome thank you for your help :)) – Carlos Perez May 29 '14 at 19:41

Cascade on delete sql

2 Answers2