How to constrain a MySQL INSERT statement with reference to a foreign key

I have created two tables. One is called 'data' and the other is called 'metadata' (I am fooling around with an example database schema I found in "Programming the Semantic Web", it is an interesting read for sure).

I am trying to restrict insertions to the 'data' table such that data can only be inserted if there exists a foreign key in the 'metadata' table.

Table definitions are below:

CREATE TABLE metadata (
        predicate SMALLINT PRIMARY KEY,
        description VARCHAR(1024) NOT NULL
);

CREATE TABLE data (
        reporting_date DATE PRIMARY KEY,
        date_entered DATE NOT NULL,
        predicate SMALLINT NOT NULL,
        value SMALLINT NOT NULL,
        FOREIGN KEY (predicate) REFERENCES metadata(predicate)
);

An example of this constraint would be something along these lines:

INSERT INTO data VALUES ("1900-01-01", "1900-01-01", 1000, 1);

That insertion should only happen IF there is an entry in the 'metadata' table with predicate of 1000, otherwise it should fail.

I have looked into the CHECK() operation, but I don't know how to reference a foreign table from within the check, nor how to check if a query returns an empty set from within a check.

Any help would be appreciated.

I am using Ver 14.12 Distrib 5.0.26, for suse-linux (x86_64).



Read more here: https://stackoverflow.com/questions/65078536/how-to-constrain-a-mysql-insert-statement-with-reference-to-a-foreign-key

Content Attribution

This content was originally published by squillyprice at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: