0

I have a column of type integer array GENRE INTEGER ARRAY NOT NULL, each value of which must be a foreign key. I tried this FOREIGN KEY(SELECT * FROM GENRE) REFERENCES PUBLIC.GENRE(ID) but obviously it won't work. Help how this can be implemented in HSQLDB.

DKosh
  • 23
  • 4
  • Don't de-normalize - especially if you need foreign keys. Create a proper one-to-many relationship –  Aug 04 '20 at 13:34
  • Maybe I'm not thinking right but what's wrong with just `FOREIGN KEY(GENRE) REFERENCES PUBLIC.GENRE(ID)`? – M Z Aug 04 '20 at 13:41
  • @MZ it won't work because `PUBLIC.GENRE(ID)` is not an array – DKosh Aug 04 '20 at 13:45
  • This can't be done with FOREIGN KEY. You can write INSERT and UPDATE triggers that check each element of the array is an existing ID. – fredt Aug 04 '20 at 19:19

0 Answers0