CHECK and Subselect, how to combine when not allowed?
I create the following 2 relation's :CREATE TABLE Vark (
varkNavn VARCHAR(30),
genre VARCHAR(20) NOT NULL,
stilart VARCHAR(20) NOT NULL,
kompositionsAr INT NOT NULL,
PRIMARY KEY (varkNavn));
CREATE TABLE Indspilning (
komponistNavn VARCHAR(30),
varkNavn VARCHAR(30),
indspilningsAr INT CHECK (indspilningsAr >= (SELECT kompositionsAr from Vark where Vark.varkNavn = varkNavn)),
indspilningsSted VARCHAR(30),
varighed DOUBLE PRECISION NOT NULL,
PRIMARY KEY (komponistNavn, varkNavn, indspilningsAr, indspilningsSted),
FOREIGN KEY (varkNavn) REFERENCES Vark(varkNavn)
ON DELETE CASCADE
ON UPDATE CASCADE);
This gives me :
"cannot use subselect in CHECK constraint expression"
I read somewhere that by creating a function and using that in the CHECK instead it would work... so tried that:
CREATE FUNCTION getKompositionsAr(text) RETURNS integer AS 'SELECT kompositionsAr from Vark where varkNavn = $1' LANGUAGE 'sql';
and retrieving the kompositionsAr by :
CHECK (indspilningsAr >= (SELECT getKompositionsAr('Numb'));
But this gives the same error...
How do I come around this !? Can it be solved or is there a better way...?
(svar bar på dansk :-) )