1

I have 3 tables, "Courses"(id, start_date), "Subscriptions"(id, assistant_id, course_id, date) and "Assistants"(id, registration_date).

Subscriptions reference Courses and Assistants with foreign keys as you see.

I need to add CHECK constraint that will prevent to create Subscription record if referenced Courses.start_date is older than referenced Assistants.registration_date. Is there a way to do this in Libre Base?

Table organization could not be changed.

I Hafid
  • 373
  • 2
  • 13

1 Answers1

2

Such a CHECK constraint cannot be created with the default engine. From the HSQLDB 1.8 documentation:

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] CHECK (<search condition>);

Adds a check constraint to the table. In the current version, a check constraint can reference only the row being inserted or updated.

This means that commands like the following from TestSelfCheckConstraints.txt produce an error:

/*e*/CREATE TABLE TC6(A CHAR, B CHAR, C CHAR, D INT, CHECK(A IN (SELECT A FROM TC5)));

So, to perform such a check, you will have to verify it ahead of time (or afterwards) using a query. This could be done for a form by adding a macro in the Events tab. See this post for ideas: https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=21414.

The default engine is rather old, so for such complex requirements it may be better to set up LibreOffice Base to use a different database engine. For example using MySQL, it is possible to set up a stored procedure trigger to do this kind of checking. See CHECK constraint in MySQL is not working.

Community
  • 1
  • 1
Jim K
  • 12,824
  • 2
  • 22
  • 51
  • 1
    The default HSQLDB engine that comes zipped inside Base, while old - version 1.8 - is also a very reliable (and free) engine. Since the Base parser is optimized for HSQLDB, unless the user has prior familiarity with MySQL it can be better to upgrade to the current HSQLDB version (2.x) instead of switching databases altogether. – Lyrl Jan 08 '16 at 21:01
  • Thank you. In my case, query was definitely the answer, turns out that my task was poorly described and I didn't have to filter the input by the date range, but the output. – I Hafid Jan 09 '16 at 21:33