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
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
);