I'm using a mariaDB instance and I would like to create a check constraint that a value must exist in another table. In the below example, TableA.Number must already exist in TableB.
TableA
Name varchar(30)
Number int
TableB
Number int
CodePudding user response:
I don't know if I understand your question correctly, but have you already tried with foreign keys?
https://mariadb.com/kb/en/foreign-keys/
CodePudding user response:
You can create a trigger to achieve the same
CREATE TRIGGER Check_exist_in_B BEFORE
INSERT ON TableA >
DECLARE ExistingNum INT;>
SET @ExistingNum : = (
SELECT NUMBER
FROM TableB
WHERE b.number = new.number
) >
IF (@ExistingNum IS NULL) THEN > SIGNAL SQLSTATE '45000' >
SET MESSAGE_TEXT = 'Not exists in B' >
END;