How to add a foreign key constraint on the elements of array data type

Question

I am trying to execute this hsql query but I am getting execetion because 'SYMPTOMS' in PATIENTVISIT is of type Array Varchar() where as 'SYMPTOM_NAME' in table SYMPTOMS is of type varchr() :

 ALTER TABLE PATIENTVISIT ADD CONSTRAINT IF NOT EXISTS 
 PATIENTVISIT_SYMPTOMS_FK FOREIGN KEY (SYMPTOMS) REFERENCES SYMPTOMS(SYMPTOM_NAME);

The exception I am getting is:

 java.sql.SQLSyntaxErrorException: incompatible data types in combination in statement [ALTER TABLE PATIENTVISIT ADD CONSTRAINT IF NOT EXISTS PATIENTVISIT_SYMPTOMS_FK FOREIGN KEY (SYMPTOMS) REFERENCES SYMPTOMS(SYMPTOM_NAME)]

How to resolve this issue or what is the workaround?

Answer 1

You want only the values in the SYSMPTOM_NAME column to be used as elements of the array in PATIENTVISIT. Only the recognized SYMPTOM_NAME values are allowed as valid symptoms.

This type of constraint cannot be defined using a FOREIGN KEY.

You can use a TRIGGER on the PATIENTVISIT table that checks all the elements of the array against those in the SYMPTOMS table and throws an exception if there is an unrecognised value.

CREATE TRIGGER CHECK_VALUES BEFORE INSERT ON PATIENTVISIT REFERENCING NEW  ROW AS NEWROW FOR EACH ROW 
BEGIN ATOMIC
 IF EXISTS (SELECT TRUE FROM UNNEST (NEWROW.SYMPTOMS) X(A) LEFT JOIN SYMPTOMS ON X.A = SYMPTOMS.SYMPTOM_NAME WHERE SYMPTOMS.SYMPTOM_NAME IS NULL)
  THEN
    SIGNAL SQLSTATE '45000';
  END IF;
END