CHECK constraint on a table

Question

I want to modify the following DDL to add CHECK constraints so that the manager of a store(FK employee_number is store table) works at the same store (FK store_code in employee table table) 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
  )

Can you change the schema at all or must you use the schema as-is? — Tim Lehner, Jun 20 '13 at 18:10
I must use the schema as is - it's not a practical implementation. — zan, Jun 20 '13 at 18:12
I flagged the other one for deletion. I messed up the model ;) — zan, Jun 20 '13 at 18:17
@zan - you shouldn't, generally, open duplicate questions. You should *edit* until your question makes sense. — Damien_The_Unbeliever, Jun 20 '13 at 18:47

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

you could use a trigger to realize this

you can look at this question for an example Throw an error in a MySQL trigger

edited May 23 '17 at 10:25

Community

1
1

answered Jun 20 '13 at 18:38

cproinger

2,258
1
17
33

CHECK constraint on a table

1 Answers1