on 2020 May 03 11:04 AM
Please help!
I have 5 tables: clients(passport_ number(PK), name, date_ of _birth), orders(id(PK), date, clients _ passport _ number(FK),hotels _ id(FK)), hotels(id(PK), name), rooms(number _ of _ room(PK), price, hotels _ id(FK)), rezervation(rooms _ number _ of _ room(FK), orders _ id(FK), reservation _ from(date), reservation _ to(date).
I want to make 2 constraints: 1)order date should be earlier than reservation_from 2)next reservation_from for the room should be later than previous reservation_to
How is it possible to realize? My teacher adviced me using control view, but I don't know how to do it.
Request clarification before answering.
Volker can tell me I'm wrong, but I don't think table CHECK constraints can include cross-table queries.
In your case, each of your constraints may require two TRIGGER statements to handle both INSERT and UPDATE statements on two tables. The following code shows one pair of triggers.
NOTE: Only one error condition was tested.
BEGIN DROP TABLE hotels; EXCEPTION WHEN OTHERS THEN END; BEGIN DROP TABLE clients; EXCEPTION WHEN OTHERS THEN END; BEGIN DROP TABLE orders; EXCEPTION WHEN OTHERS THEN END; BEGIN DROP TABLE rooms; EXCEPTION WHEN OTHERS THEN END; BEGIN DROP TABLE hotels; EXCEPTION WHEN OTHERS THEN END; BEGIN DROP TABLE rezervation; EXCEPTION WHEN OTHERS THEN END; CREATE TABLE hotels( id INTEGER PRIMARY KEY, name VARCHAR ( 50 )); CREATE TABLE clients( passport_number INTEGER PRIMARY KEY, name VARCHAR ( 50 ), date_of_birth DATE ); CREATE TABLE orders( id INTEGER PRIMARY KEY, orders_date DATE, clients_passport_number INTEGER REFERENCES clients ( passport_number ), hotels_id INTEGER REFERENCES hotels ( id ) ); CREATE TABLE rooms( number_of_room INTEGER PRIMARY KEY, price DECIMAL ( 9, 2 ), hotels_id INTEGER REFERENCES hotels ( id ) ); CREATE TABLE rezervation( rooms_number_of_room INTEGER REFERENCES rooms ( number_of_room ), orders_id INTEGER REFERENCES orders ( id ), reservation_from DATE, reservation_to DATE ); CREATE TRIGGER orders1 BEFORE INSERT, UPDATE ON orders REFERENCING NEW AS new_orders FOR EACH ROW BEGIN IF EXISTS ( SELECT * FROM rezervation WHERE new_orders.orders_date > rezervation.reservation_from AND new_orders.id = rezervation.orders_id ) THEN ROLLBACK TRIGGER WITH RAISERROR 99999 'orders_date should be earlier than reservation_from'; END IF; END; CREATE TRIGGER rezervation1 BEFORE INSERT, UPDATE ON rezervation REFERENCING NEW AS new_rezervation FOR EACH ROW BEGIN IF EXISTS ( SELECT * FROM orders WHERE orders.orders_date > new_rezervation.reservation_from AND orders.id = new_rezervation.orders_id ) THEN ROLLBACK TRIGGER WITH RAISERROR 99999 'orders_date should be earlier than reservation_from'; END IF; END; INSERT hotels VALUES ( 111, 'xxx' ); INSERT clients VALUES ( 999, 'aaa', '1901-01-01' ); INSERT orders VALUES ( 222, '2020-05-03', 999, 111 ); INSERT rooms VALUES ( 666, 200.00, 111 ); INSERT rezervation VALUES ( 666, 222, '2020-05-04', '2020-05-05' ); INSERT orders VALUES ( 223, '2020-05-11', 999, 111 ); INSERT rezervation VALUES ( 666, 223, '2020-05-10', '2020-05-12' ); Could not execute statement. RAISERROR executed: orders_date should be earlier than reservation_from SQLCODE=-99999, ODBC 3 State="HY000" Line 1, column 1 INSERT rezervation VALUES ( 666, 223, '2020-05-10', '2020-05-12' )
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Well, I do (very rarely!) use check constraints within a table T1 that check values of a different table T2, so this is actually possible.
Of course they are only activated when data in T1 are modified, they will not prevent modifications on T2 that violate the checks. In my cases the related table T2 is generally some kind of code table basically read only for regular users so this was no problem here. Otherwise, one would need a trigger on T2 to prevent undesired modifications (or adapt the rows in T1).
See also this related FAQ...
Well done... for the casual reader, here's a snippet from "this related FAQ" Volker mentions...
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)
> they will not prevent modifications on T2 that violate the checks
Adding a "mirror" CHECK on table T2 that did a SELECT COUNT(*) FROM T1 would solve that problem, wouldn't it?
Adding a "mirror" CHECK ...
I guess so but it may be difficult in case rows are modified within the same transactions in both tables, and isolation issues might arise...
One other pitfall I stumbled over related to database reloads: In contrast to FOREIGN KEYs, CHECK CONSTRAINTS are created as part of the CREATE TABLE statement itself, and so they will do their checks when data is loaded. So one has to make sure data for T2 has to be loaded before data for T1 is loaded, i.e. the order in which database tables are created and loaded becomes important. For FOREIGN KEY clauses, this is no problem as they are created when all data is already loaded.
I'm having trouble seeing how any of that can be a problem...
Isolation levels apply to updates made by separate concurrent transactions, not the same one... and besides that, constraints don't update anything.
The CHECK constraints discussed here won't fail if rows are missing (not loaded yet) from the "other table", because the COUNT would be zero.
The mirror checks are necessary precisely because you cannot control the order of UPDATE statements; UPDATE T1 can make T1.col1 invalid with respect to T2.col2, and UPDATE T2 can make T2.col2 invalid with respect to T1.col1.
I suppose you could create a CHECK constraint that would have a problem when the data is reloaded in the "wrong" order. For example, you could implement a foreign key relationship using a CHECK constraint.
Might be overthinking on my part... The checks I have used do not count() against a different table but basically check that a column can only be not null when there's a FK to a different table and the row in that particular table has a certain flag set or not. Say, a product (*) is only allowed to have a particular value (say, a size) when the according product category allows or requires such an attribute. That design would be helpful when product categories are added now and then and when their attributes may change over time. As to the reload issue: In those cases, the product category table need to be loaded before any product can be loaded, otherwise a reload would fail because of failing checks.
(*) This is just a simple example, it's not related to my work field.
> Might be overthinking on my part...
That doesn't matter... what does matter is you pointed out how CHECK constraints are much more powerful than I realized.
Any thing that eliminates a CREATE TRIGGER is a good thing, for errors inside triggers can be very hard to find 🙂
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.