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.TYPE_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.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.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.TYPE_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