check constraint questions

[kaming]

Dear all,

Is it possible to define a check constraint on a field that the field value
must exist in another table non-unique field? how to write the create table
DDL?

i.e
TABLE A
TYPE, REGION
Apple, A
Apple, B

TABLE B
TYPE,COST
Apple, $1,000
Orange, $2,000

Could a constraint be created in TABLE B on the TYPE field that the TYPE
must exist in TABLE A??

Thanks in advance!

Henry

[dotyet]

you can do that with primary key/foreign key concept, defining a child
and parent relationship. table A will be the parent and table B will be
the child.

if the type in table A does not exist a corresponding type in table B
cannot be created.
------------------------------------------------
-- DDL Statements for table "DB2INST1". "A"
------------------------------------------------

CREATE TABLE "DB2INST1". "A" (
"TYPE_ID" INTEGER NOT NULL ,
"TYPE_NAME" VARCHAR(20) )
IN "USERSPACE1 " ;

-- DDL Statements for primary key on Table "DB2INST1". "A"

ALTER TABLE "DB2INST1". "A"
ADD PRIMARY KEY
("TYPE_ID");
------------------------------------------------
-- DDL Statements for table "DB2INST1". "B"
------------------------------------------------

CREATE TABLE "DB2INST1". "B" (
"TYPE_ID" INTEGER NOT NULL ,
"COST" INTEGER )
IN "USERSPACE1 " ;

-- DDL Statements for foreign keys on Table "DB2INST1". "B"

ALTER TABLE "DB2INST1". "B"
ADD CONSTRAINT "TYPE_FK" FOREIGN KEY
("TYPE_ID")
REFERENCES "DB2INST1". "A"
("TYPE_ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
############### ########

$ db2 "insert into a(type_id, type_name) values (10001, 'Apples')"
DB20000I The SQL command completed successfully.
$ db2 "insert into a(type_id, type_name) values (10002, 'Oranges')"
DB20000I The SQL command completed successfully.
$ db2 "insert into b(type_id, cost) values (10001, 300)"
DB20000I The SQL command completed successfully.
$ db2 "insert into b(type_id, cost) values (10002, 200)"
DB20000I The SQL command completed successfully.
$ db2 "insert into b(type_id, cost) values (10004, 700)"
DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned: SQL0530N The insert or update value of the FOREIGN KEY
"DB2INST1.B.TYP E_FK" is not equal to any value of the parent key of the
parent table. SQLSTATE=23503

the last SQL statement and it's result, is what you wanted.... right?
regards,
dotyet

[kaming]

Dotyet,

Thanks very much!, you have spotted the key problem for me.

Regards,

Henry
"dotyet" <do****@yahoo.c om> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

you can do that with primary key/foreign key concept, defining a child
and parent relationship. table A will be the parent and table B will be
the child.

if the type in table A does not exist a corresponding type in table B
cannot be created.
------------------------------------------------
-- DDL Statements for table "DB2INST1". "A"
------------------------------------------------

CREATE TABLE "DB2INST1". "A" (
"TYPE_ID" INTEGER NOT NULL ,
"TYPE_NAME" VARCHAR(20) )
IN "USERSPACE1 " ;

-- DDL Statements for primary key on Table "DB2INST1". "A"

ALTER TABLE "DB2INST1". "A"
ADD PRIMARY KEY
("TYPE_ID");
------------------------------------------------
-- DDL Statements for table "DB2INST1". "B"
------------------------------------------------

CREATE TABLE "DB2INST1". "B" (
"TYPE_ID" INTEGER NOT NULL ,
"COST" INTEGER )
IN "USERSPACE1 " ;

-- DDL Statements for foreign keys on Table "DB2INST1". "B"

ALTER TABLE "DB2INST1". "B"
ADD CONSTRAINT "TYPE_FK" FOREIGN KEY
("TYPE_ID")
REFERENCES "DB2INST1". "A"
("TYPE_ID")
ON DELETE NO ACTION
ON UPDATE NO ACTION
ENFORCED
ENABLE QUERY OPTIMIZATION;
############### ########

$ db2 "insert into a(type_id, type_name) values (10001, 'Apples')"
DB20000I The SQL command completed successfully.
$ db2 "insert into a(type_id, type_name) values (10002, 'Oranges')"
DB20000I The SQL command completed successfully.
$ db2 "insert into b(type_id, cost) values (10001, 300)"
DB20000I The SQL command completed successfully.
$ db2 "insert into b(type_id, cost) values (10002, 200)"
DB20000I The SQL command completed successfully.
$ db2 "insert into b(type_id, cost) values (10004, 700)"
DB21034E The command was processed as an SQL statement because it was
not a valid Command Line Processor command. During SQL processing it
returned: SQL0530N The insert or update value of the FOREIGN KEY
"DB2INST1.B.TYP E_FK" is not equal to any value of the parent key of the
parent table. SQLSTATE=23503

the last SQL statement and it's result, is what you wanted.... right?
regards,
dotyet