MySQL doesn't prevent deletion of rows where primary key is associated with another table-CodePudding

tested this on postgreSQL and it doesn't let me delete any row(primary key) associated with another table. Is MySQL different or is there is a way to do that here in Mysql?.

CREATE TABLE account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30)
);

CREATE TABLE transaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id BIGINT REFERENCES account(id)
);

INSERT INTO account (name) VALUES ('john');  //account_id = 1
INSERT INTO transaction (account_id) VALUES (1);

DELETE FROM account where id = 1;

);

when I delete an account row in account table, mySQL doesn't prevent the deletion of it.

CodePudding user response：

Here is the example for MYSQL. You need add the foreign key with update and delete constrains. You can also read through the documents for more details. Foreign Key

CREATE TABLE account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(30)
);

CREATE TABLE transaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id BIGINT,
    FOREIGN KEY (account_id) REFERENCES account(id) ON UPDATE CASCADE ON DELETE RESTRICT
);

INSERT INTO account (name) VALUES ('john');
INSERT INTO transaction (account_id) VALUES (1);

DELETE FROM account where id = 1;

-- 18:32:14 DELETE FROM account where id = 1    Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`transaction`, CONSTRAINT `transaction_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON UPDATE CASCADE) 0.039 sec