Details
-
New Feature
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Do
-
1.0.11
-
CentOS 7
Description
Current culumnstore can't support constraints FOREIGN KEY,UNIQUE,PRIMARY KEY, but these constraints is very important for avoid logic error from applications, I try use TRIGGER to avoid the limitations, but I found NOT an integrated transaction in an INSERT statement:
CREATE TABLE head
(id int PRIMARY KEY,
sVal varchar(50)
) ENGINE=INNODB;
INSERT INTO head(id,sVal) values(1,'Val1'),(2,'Val3'),(3,'Val3');
CREATE TABLE detail_inno
(
id int AUTO_INCREMENT PRIMARY KEY,
fk int NOT NULL,
sData varchar(30)
)ENGINE=INNODB;
CREATE TABLE detail_columnstore
(
id int,
fk int NOT NULL,
sData varchar(30)
)ENGINE=COLUMNSTORE COMMENT = 'autoincrement=id';;
DELIMITER //
CREATE TRIGGER fktest_inno BEFORE INSERT ON detail_inno FOR EACH ROW
BEGIN
IF NOT EXISTS(SELECT * FROM head WHERE id=NEW.fk) THEN
SIGNAL sqlstate '45001' set message_text = "Force Key fk NOT exists in table head ! ";
END IF;
END//
CREATE TRIGGER fktest_columnstore BEFORE INSERT ON detail_columnstore FOR EACH ROW
BEGIN
IF NOT EXISTS(SELECT * FROM head WHERE id=NEW.fk) THEN
SIGNAL sqlstate '45001' set message_text = "Force Key fk NOT exists in table head ! ";
END IF;
END//
DELIMITER ;
INSERT INTO detail_inno(fk,sData) VALUES(1,'data1'),(2,'data2'),(4,'data4');--GOOD: NOT any records inserted at here.
INSERT INTO detail_columnstore(fk,sData) VALUES(1,'data1'),(2,'data2'),(4,'data4');-- BAD: id 1,2 is inserted at here, id 4 not inserted.