check-constraint

[Jagdip Singh]

Hi all,
I have four tables
create table employee
(emp_num integer not null,
emp_name varchar(20),
emp_commision real,
emp_salary real
);
create table clients
(c_num integer not null,
c_name varchar(20),
c_addr varchar(20),
c_city varchar(15),
c_state varchar(2),
c_zip integer,
c_phone integer
);
create table products
(p_num integer not null,
p_quantity integer,
p_price real
);
create table sales
(s_num integer not null,
p_num integer not null,
s_quantity integer not null,
s_amount real not null,
emp_num integer not null,
c_num integer not null
);

I want to put constraint on s_amount in sales table which is "Value in
s_amount should be s_quantity*products.p_price"
how can i do it syntactically in db2

regards,
Jagdip

[Knut Stolze]

Jagdip Singh <jx*****@cs.rit.edu> wrote:

Hi all,
I have four tables

create table products
(p_num integer not null,
p_quantity integer,
p_price real
);
create table sales
(s_num integer not null,
p_num integer not null,
s_quantity integer not null,
s_amount real not null,
emp_num integer not null,
c_num integer not null
);

I want to put constraint on s_amount in sales table which is "Value in
s_amount should be s_quantity*products.p_price"
how can i do it syntactically in db2

You can't access other tables in a check constraint. But you have the
following options:

(1) Create triggers that verify S_AMOUNT on every insert and update on the
SALES table
(2) Copy the P_PRICE column to the SALES table (also using triggers) and
then use a check constraint
(3) Check the condition in your application before or after you modify data
in the SALES table.

Option (3) maybe not very smart if you have (or will have) more than
application. It also makes the app more complex and complicated.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena