Re: Unique Constraint over multiple table
Fred Toussi via Hsqldb-user <[email protected]> Wed, 30 Nov 2022 15:07:00 +0000
| Newsgroups | gmane.comp.java.hsqldb.user |
|---|---|
| Message-ID | <[email protected]> |
--===============3571725556891339691== Content-Type: multipart/alternative; boundary=62e07aafe3dd4a64ab79414a4297bb45 --62e07aafe3dd4a64ab79414a4297bb45 Content-Type: text/plain It is not possible to create a constraint on a view. You can create views on each table as for example "SELECT * FROM PM_LINK_CMD" then create INSTEAD OF INSERT triggers on the views. The code of the trigger should first check if the new inserted ids are not in the other table before inserting into the view's table. Your app inserts into the views you created, rather than the underlying tables. Fred On Wed, Nov 30, 2022, at 14:36, Thorsten wrote: > Hello. > > I am interested in creating a unique constraint spanning multiple tables. The internet suggests creating a view table and than creating a unique for that view. > > So I did > > CREATE VIEW EV_KEY AS SELECT c.CMD_META_ID, d.DP_ID FROM PM_LINK_CMD as c JOIN PM_LINK_DP d on d.PM_LINK_ID=c.PM_LINK_ID > CREATE UNIQUE INDEX event_link ON EV_KEY(CMD_META_ID,DP_ID) > > > That aproach seems to be unsupported for HSQLDB. Is there a way to have a unique constraint that joins multiple tables in hsqldb? > > Best regards, > > Thorsten Goetzke > > > _______________________________________________ > Hsqldb-user mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/hsqldb-user > --62e07aafe3dd4a64ab79414a4297bb45 Content-Type: text/html <!DOCTYPE html><html><head><title></title><style type="text/css">p.MsoNormal,p.MsoNoSpacing{margin:0}</style></head><body><div style="font-family:Arial;">It is not possible to create a constraint on a view.<br></div><div style="font-family:Arial;"><br></div><div style="font-family:Arial;">You can create views on each table as for example "SELECT * FROM PM_LINK_CMD" then create INSTEAD OF INSERT triggers on the views. The code of the trigger should first check if the new inserted ids are not in the other table before inserting into the view's table. Your app inserts into the views you created, rather than the underlying tables.<br></div><div style="font-family:Arial;"><br></div><div style="font-family:Arial;">Fred<br></div><div style="font-family:Arial;"><br></div><div>On Wed, Nov 30, 2022, at 14:36, Thorsten wrote:<br></div><blockquote type="cite" id="qt" style=""><p>Hello.<br></p><p>I am interested in creating a unique constraint spanning multiple tables. The internet suggests creating a view table and than creating a unique for that view.<br></p><p>So I did <br></p><pre style="background-color:rgb(43, 43, 43);color:rgb(169, 183, 198);font-family:"JetBrains Mono", monospace;">CREATE VIEW EV_KEY AS SELECT c.CMD_META_ID, d.DP_ID FROM PM_LINK_CMD as c JOIN PM_LINK_DP d on d.PM_LINK_ID=c.PM_LINK_ID CREATE UNIQUE INDEX event_link ON EV_KEY(CMD_META_ID,DP_ID)<br></pre><p><br></p><p>That aproach seems to be unsupported for HSQLDB. Is there a way to have a unique constraint that joins multiple tables in hsqldb? <br></p><p>Best regards,<br></p><p>Thorsten Goetzke<br></p><div><br></div><div>_______________________________________________<br></div><div>Hsqldb-user mailing list<br></div><div><a href="mailto:[email protected]">[email protected]</a><br></div><div><a href="https://lists.sourceforge.net/lists/listinfo/hsqldb-user">https://lists.sourceforge.net/lists/listinfo/hsqldb-user</a><br></div><div><br></div></blockquote><div style="font-family:Arial;"><br></div></body></html> --62e07aafe3dd4a64ab79414a4297bb45-- --===============3571725556891339691== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline --===============3571725556891339691== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ Hsqldb-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/hsqldb-user --===============3571725556891339691==--