check constraint against another table

[Former Member]

HELP PLEASE!!

I am trying to figure out how to write a check constraint. I have 2 tables, the first table name is wardround the second is nurse_works_on_ward. They both have a foreign key of employeeNo (nurse's employee Nos) from a third table (employees). The wardround table has the employeeNo as a reference for the nurse who has overall responsibility for the ward. This nurse cannot be one who appears in the nurse_works_on_ward table (this is a relation for relationship table). This is an alter table change.

I know it starts something along the lines of :

ADD CONSTRAINT NurseCannotBeMatron CHECK (EmployeeNo (then I'm totally lost so have written in plane English!!) does not equal the employeeNo in wardround where the wardround (ward, employeeNo, date) is the same as nurseworksonward (ward, employeeNo, date)

I hope I've explained this well enough for someone to help me!!

many thanks

SQL beginner

[justin_willey]

Correction - Volker points out that a table constraint can refer to other tables - as to whether a trigger or constraint would be more efficient in this case - you would need to experiment.

---

I am pretty sure (but I can't find a definite reference in the docs) that table constraints can only use data in the row that is being inserted. If you want to check something in another table you need to can use a trigger - in this case probably a row level BEFORE INSERT trigger, that would reject the new row if it failed to pass your test eg (definitely NOT tested!)

CREATE TRIGGER NurseCannotBeMatron
  BEFORE INSERT ON Wardround
  REFERENCING NEW AS new_Wardround
  FOR EACH ROW
BEGIN
 DECLARE err_user_error EXCEPTION FOR SQLSTATE '99999';
 IF new_Wardround.employeeNo in (select employeeNo from nurse_works_on_ward where new_Wardround.ward = nurse_works_on_ward.ward) 
   THEN SIGNAL err_user_error;
 END IF;
 END;

[Former Member]

Hi Justin and thanks for the reply, unfortunately we cannot use the trigger as we have not been shown this tool. I wondered if this might be anywhere near where I want to be:

ALTER TABLE wardround ADD CONSTRAINT NurseCannotBeMatron CHECK (EmployeeNo <> (SELECT m.EmployeeNo FROM nurse_works_on_ward m, wardround n WHERE m.wardNo = n.wardno AND m.dateperformed =n.dateperfomed)

dateperformed and wardNo are the primary key, so if these two columns match between the tables compare the employeenos which shouldn't match.

Many thanks

SQL beginner

[justin_willey]

The logic is probably OK, but as I said, I don't believe that a table constraint can look beyond an individual row in the table - whereas you need to check values in the nurse_works_on_ward table when you are inserting into the wardround table.

[VolkerBarth]

I am pretty sure (but I can't find a definite reference in the docs) that table constraints can only use data in the row that is being inserted.

No, that's not true AFAIK. A table check just checks a specified condition, and if it evaluates to false, the data modification on the table will be rejected.

You can certainly use a TABLE CHECK CONSTRAINT to assure that a value in the current table does somehow "fit" to data in another table. The drawback being that data modifications on the other table would not be checked by the check on the current table - it will only check for modifications on its own data. Furthermore, CHECKS are only done for INSERT and UPDATE statements, not for DELETEs - again AFAIK. And the performance may be bad since AFAIK a check does have to check all rows whereas a trigger can be used to check just the modified data.

Without a real schema it's not possible to suggest a solution, and I guess the conditions here are not really working, however something like the following might work - note, it uses a NOT IN predicate:

ALTER TABLE wardround
   ADD CONSTRAINT NurseCannotBeMatron
   CHECK ((SELECT COUNT(*)
     FROM nurse_works_on_ward nwow, wardround wr
     WHERE nwow.wardNo = n.wardNo
        AND nwow.EmployeeNo = wr.EmployeeNo
        AND nwow.dateperformed = wr.dateperfomed) = 0)


(I'm not sure if this will work as the check may be done before the data is entered, so the condition may still evaluate to true before a nurse is assigned a second time, which should be avoided...)
As stated, the drawback of such an approach would be the missing check if data in the second table (here nurse_works_on_ward) would be modified afterwards.

[justin_willey]

This is wrong - see Volker's comment

[VolkerBarth]

FWIW, may you have thought of MS SQL? AFAIK, they don't allow joins and sub-selects in CHECK CONSTRAINTs, so you have to use user-defined functions for such requirements.

Been there, unfortunately:)

[Former Member]

Justin is very correct in suggesting Before timing, row-level, insert and update triggers as those are more flexible and allow for easier customization. But in the case of such an anti-reference you could use a check constraint for that. This is something the comes up when you want to exclude cases.

You can reference other tables with EXISTS and NOT EXISTS checks like in this example:

drop table if exists employees_on_floor ;
create table employees_on_floor ( EmpOnFloorID integer , DepartmentID integer, station char(255));
alter table employees_on_floor 
     Add constraint "ThisEmployeeIsNotAllowedOnTheFloor" 
       check( not exists (select 1 from Employees E where E.EmployeeID = EmpOnFloorID));
insert into employees_on_floor values 
   ( 101, 501, 'This test should fail because there is no EmpID==101 in the system currently.');


You could also have used [NOT] ANY or [NOT] IN whenever that makes more sense;
but there you would select the column or expression to compare on.
Note: I used the sample demo.db from version 12 for this.

[VolkerBarth]

Hm, that sample would certainly be better served with a simple FK relationship, wouldn't it?

FWIW: We have used such cross-table CHECK CONSTRAINTs rarely, and only in cases where a FK relationship would not be sufficient, say if a dependancy on a FK combined with a particular column value from the parent table would be required... - and generally only in cases where the parent table would be some "code/lookup table" that can not be modified by users...

Besides that, I certainly second your recommendation to use a BEFORE INSERT/UPDATE trigger here - or a different schema as suggested.

[Former Member]

Hey Volker,

For some reason I did not see your post. That would have been a good answer.

My example was actually a Not-Fkey relationship due to the 'NOT' in the NOT EXISTS check. But you are correct that the customer could use a FKey for this. The fkey is again not as flexible as a trigger or check constraint but it could indeed be made to work.

[VolkerBarth]

IMHO, a better design decision to prevent a nurse from being assigned both as "matron" and as "worker" would be to add another table that contains the relationship between a nurse and its work time (dateperformed). In that table, you could specify the ward number and the role (matron vs. "worker"), and that would enable easy checks that only one role can be taken by one nurse on any day...

A check constraint or unique key could ensure that only one nurse can be "matron" on any ward at any day.