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).