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.

asked 03 May, 10:04

nik12's gravatar image

nik12
252
accept rate: 0%


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' )
permanent link

answered 03 May, 14:57

Breck%20Carter's gravatar image

Breck Carter
30.1k490672976
accept rate: 20%

edited 03 May, 16:00

1

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...

(04 May, 02:18) Volker Barth
Replies hidden

Wow, thank you!

(04 May, 07:16) nik12

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?

(04 May, 09:23) Breck Carter

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.

(04 May, 11:21) Volker Barth

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.

(04 May, 12:51) Breck Carter

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.

(04 May, 14:29) Volker Barth
1

> 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 :)

(05 May, 09:06) Breck Carter
showing 1 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×11

question asked: 03 May, 10:04

question was seen: 112 times

last updated: 05 May, 09:06