adding CHECK to impose constraints on more than on

Question

I want to modify the following DDL to add CHECK constraints so that the manager of a store works at the same store and a store supplies all the products if its type is 'local'.

Can anyone help?

CREATE TABLE employee(
  employee_number CHAR(5) NOT NULL,
  name VARCHAR(30),
  store_code CHAR(5)
  PRIMARY KEY(employee_number),
  FOREIGN KEY(store_code) REFERENCES store
  )

CREATE TABLE store(
  store_code CHAR(5) NOT NULL,
  type VARCHAR(15),
  employee_number CHAR(5),
  PRIMARY KEY(store_code),
  FOREIGN KEY(employee_number) REFERENCES employee
  )


CREATE TABLE product(
  product_code CHAR(5) NOT NULL,
  description VARCHAR(150),
  cost DEC(10,2),
  PRIMARY KEY(product_code)
  )

CREATE TABLE stocks(
  store_code CHAR(5) NOT NULL,
  product_code CHAR(5) NOT NULL,
  PRIMARY KEY(product_code, store_code),
  FOREIGN KEY(product_key) REFERENCES product,
  FOREIGN KEY(store_code) REFERENCES store
  )

Answer 1

You can do this by changing your primary key in works_at, then adding unique constraints to ensure the uniqueness. This will be better than using check constraints:

CREATE TABLE works_at(
  employee_number CHAR(5),
  store_code CHAR(5),
  PRIMARY KEY(employee_number, store_code),
  FOREIGN KEY(employee_number) REFERENCES employee,
  FOREIGN KEY(store_code) REFERENCES store,
  CONSTRAINT UQ_Works_at_employee_number UNIQUE NONCLUSTERED(employee_number) -- ENSURES EMPLOYEE CAN ONLY WORK AT ONE STORE
  )

Then your manages table can reference works_at to ensure they manage the store they work at:

CREATE TABLE manages(
  employee_number CHAR(5),
  store_code CHAR(5),
  PRIMARY KEY(store_code),
  FOREIGN KEY(employee_number, store_code) REFERENCES works_at (employee_number, store_code)
  )

With regard to your second part, I don't see a way of enforcing the fact that stocks must contain all stores and all products, and this also seems kind of pointless, you are essentially asking for a table that is just a cross join of two other tables.

Answer 2

To ensure that a manager actually works in the store, I would do this:

drop table manages;

alter table works_at
add column isManager bit default 0;

Ensuring that every store stocks every product is best done with a trigger, as mentioned in the other answers.

You should also consider the following points.

1. char(5) is not necessarily the best datatype for primary key fields
2. Having the employee number as the primary key might not be such a good idea. In real life there could be transfers, or someone might work part time at more than one store.
3. If every store is supposed to stock every item, the stocks table might not be useful. An inventory table, which include quantity on hand might be better.

Answer 3

I see options though the implementation changes depending on the RDBMS:

• Check constraint calling a function/stored procedure.
• Trigger, or custom stored procedure.
• Using insert-select syntax

Check constraint calling a function

You will have to create your function, and then utilize it when creating the constraint.

Examples/Sources:

• Sub queries in check constraint
• http://blog.christosoft.de/2012/08/mysql-check-constraint/
• SQL Server: http://benchmarkitconsulting.com/colin-stasiuk/2011/08/08/check-constraints-that-call-functions/
• Can I call a user-defined function from a column CHECK constraint?

Custom Stored Procedure/Function

In addition to the proposed solution with triggers, another option is to create a stored procedure that is used to insert the data.

The stored procedure performs the validation, and it the conditions are not met does not insert the data.

Using Insert with Select

The below would ensure that a manage entry is not added unless an employee works at the specific store.

INSERT INTO manager (emploee_number, store_code) AS
SELECT distinct employee_number,
       store_code
FROM manages
WHERE store_code = INPUT_STORE_CODE
  AND employee_number = INPUT_EMPLOYEE_NUMBER

Answer 4

A CHECK constraint is limited to a single row in a single table.

If you really want to implement that kind of check I guess you would have to use triggers.

Note that you have modeled a many-to-many relationship for works-at and manages. If it were a many-to-one it would have been doable because it would look like Employee(number, name, works_at_store_code, manages_store_code). And the constraint would simply be CHECK (manages_store_code is null or manages_store_code = works_at_store_code).