Why do I get a DELETE ON cascade syntax error

user_13745926 0
Viewed 78

I am trying to make the groups in my messaging system delete the group, the messages and the members using delete on cascade error. My code :

"CREATE TABLE `groups` (
          groups_id int AUTO_INCREMENT PRIMARY KEY,
          owner_id int NOT NULL,
          group_name varchar(255)
        )",
  "CREATE TABLE `groups_members` (
          groups_members_id int AUTO_INCREMENT PRIMARY KEY,
          groups_id int NOT NULL,
          user_id int,
          FOREIGN KEY (groups_id)
              REFERENCES groups(groups_id)
              DELETE ON CASCADE
        )",
  "CREATE TABLE `groups_contents` (
          groups_contents_id int AUTO_INCREMENT PRIMARY KEY,
          user_id int,
          groups_id int,
          date_time DATETIME,
          message varchar(1000),
          FOREIGN KEY (groups_id)
              REFERENCES groups(groups_id)
              DELETE ON cascade
        )"

The error it gives me: ERROR ON STATEMENT [CREATE TABLE groups_members ( groups_members_id int AUTO_INCREMENT PRIMARY KEY, groups_id int NOT NULL, user_id int, CONSTRAINT banana FOREIGN KEY (groups_id) REFERENCES groups(groups_id) DELETE ON CASCADE )] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELETE ON CASCADE )' at line 8

mysql
1 Answers

You have some minor bugs in your code

Groups is a reserved word, so it has to be in backticks.

The syntax is ON DELETE and not DELETE ON

CREATE TABLE `groups` (
    groups_id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    group_name VARCHAR(255)
);
CREATE TABLE `groups_members` (
          groups_members_id int AUTO_INCREMENT PRIMARY KEY,
          groups_id int NOT NULL,
          user_id int,
          FOREIGN KEY (groups_id)
              REFERENCES `groups` (groups_id)
              ON DELETE  CASCADE
        );
CREATE TABLE `groups_contents` (
          groups_contents_id int AUTO_INCREMENT PRIMARY KEY,
          user_id int,
          groups_id int,
          date_time DATETIME,
          message varchar(1000),
          FOREIGN KEY (groups_id)
              REFERENCES  `groups` (groups_id)
              ON DELETE  cascade
        );
user_51935360
Related