CODEWITHSUNDEEP
sqlsql-servertriggersconstraints
Jelle
Jelle

Reputation: 798

SQL constraint multiple tables

In my database i have two tables. The first table contains a date on which the shop opens. The second table contains a date on which a customer enters the shop.

I need a constraint to make sure the customer always enters the shop after the shop opens. How can i realize this constraint A trigger / constraint / stored procedure?

Upvotes: 0

Views: 412

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

If you're happy to change the structure of your tables slightly, you can have the constraint enforced by the database system. You add an additional key to your shop table that includes the columns for an existing key + the shop open date.

You then add the shop open date as an extra column to the customer table and then either replace the existing foreign key or add a new one that references the new key mentioned above, so that you know that the shop open date stored in this table faithfully reflects that actual value in the shop table.

A simple check constraint can then compare the two column values within the customer table.

If you wish to hide the existence of this extra column from existing/external code, you rename your customer table and then produce a customer view without the shop open date. All you need then is an INSTEAD OF INSERT trigger to populate the shop open date when new customers are inserted.

(If you do choose to keep your existing structure exactly as it currently is, remember that you need at least triggers for insert and update in the customers table and a trigger for update in the shop table, so that if a shops open date is changed later you don't end up with invalid data)

Upvotes: 1

Related Questions