Create a check constraint that a value exists in another table

Question

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

score 2 · Answer 1 · answered Apr 27 '22 at 18:29

2

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/

answered Apr 27 '22 at 18:29

score 0 · Answer 2 · answered Apr 27 '22 at 18:58

0

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;

answered Apr 27 '22 at 18:58

Anugrah Gupta

11
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 28 '22 at 03:06
Refer to https://stackoverflow.com/questions/28293567/sql-server-2012-invoke-user-defined-function-in-check-constraint – Glen Feb 07 '23 at 12:19

Create a check constraint that a value exists in another table

TableA

TableB

2 Answers2