NOT foreign key in UDB V8.2 LINUX

[Bob Stearns]

For good and sufficient reasons I wish to insure that a primary key of
table 1 is not a primary key of table 2. The following does not work:

ALTER TABLE IS3.AUCTION_SUPER_CATEGORIES
ADD CONSTRAINT code_not_fk
check(code not in
(select code from IS3.AUCTION_CATEGORIES
where auction_id=auction_id))

Is there any way other than a trigger (or program checks) to write this?

[Brian Tkatch]

On Thu, 08 Mar 2007 18:28:49 -0500, Bob Stearns
<rs**********@charter.netwrote:

>For good and sufficient reasons I wish to insure that a primary key of
table 1 is not a primary key of table 2. The following does not work:

ALTER TABLE IS3.AUCTION_SUPER_CATEGORIES
ADD CONSTRAINT code_not_fk
check(code not in
(select code from IS3.AUCTION_CATEGORIES
where auction_id=auction_id))

Is there any way other than a trigger (or program checks) to write this?

Here's a pseudo-answer.

Instead of doing INSERT/UPDATE on the TABLE, use a VIEW instead. The
VIEW could use WITH CHECK OPTION to force the business rule.

B.

[Brian Tkatch]

On Thu, 08 Mar 2007 18:28:49 -0500, Bob Stearns
<rs**********@charter.netwrote:

>For good and sufficient reasons I wish to insure that a primary key of
table 1 is not a primary key of table 2. The following does not work:

ALTER TABLE IS3.AUCTION_SUPER_CATEGORIES
ADD CONSTRAINT code_not_fk
check(code not in
(select code from IS3.AUCTION_CATEGORIES
where auction_id=auction_id))

Is there any way other than a trigger (or program checks) to write this?

Another action would be to CREATE a TABLE with an FK, always LOAD data
into it, and throw exceptions into a second TABLE. That second TABLE
would be the real one.

Yes, yes, not an answer, i was just thinking about it. :)

B.

[--CELKO--]

>For good and sufficient reasons I wish to insure that a primary key of table 1 is not a primary key of table 2. <<

> The following does not work:

ALTER TABLE IS3.AUCTION_SUPER_CATEGORIES
ADD CONSTRAINT code_not_fk
check(code not in
(select code from IS3.AUCTION_CATEGORIES
where auction_id=auction_id));
<<

What **kind of code** did you mean? You need ISO-11179 names here.
To be is to be something in particular. And shouldn't you qualify
auction_id? Then let's follow Mother Celko's formattign rules about
key words in uppercase.

ALTER TABLE IS3.auction_super_categories
ADD CONSTRAINT vague_code_not_fk
CHECK(vague_code NOT IN
(SELECT vague_code
FROM IS3.auction_categories AS A
WHERE auction_super_categories.auction_id =
A.auction_id));

It still has problems, but at least it looks nice now :)

The classic scenario calls for a root class with all the common
attributes and then specialized sub-classes under it. As an example,
let's take the class of Vehicles and find an industry standard
identifier (VIN), and add two mutually exclusive sub-classes, Sport
utility vehicles and sedans ('SUV', 'SED').

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type),
..);

Notice the overlapping candidate keys. I then use a compound candidate
key (vin, vehicle_type) and a constraint in each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table. Add some DRI actions and you are done:

CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
CHECK(vehicle_type = 'SUV'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type = 'SED'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

I can continue to build a hierarchy like this. For example, if I had
a Sedans table that broke down into two-door and four-door sedans, I
could a schema like this:

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
CHECK(vehicle_type = '2DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
CHECK(vehicle_type = '4DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans (vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

The idea is to build a chain of identifiers and types in a UNIQUE()
constraint that go up the tree when you use a REFERENCES constraint.
Obviously, you can do variants of this trick to get different class
structures.

If an entity doesn't have to be exclusively one subtype, you play with
the root of the class hierarchy:

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
PRIMARY KEY (vin, vehicle_type),
..);

Now start hiding all this stuff in VIEWs immediately and add an
INSTEAD OF trigger to those VIEWs.

Another approach is to design a Dewey Decimal hierarchical
auction_categories and put restrictions on it based on ranges.

[Tonkuma]

On Mar 9, 8:28 am, Bob Stearns <[e-mail]:

For good and sufficient reasons I wish to insure that a primary key of
table 1 is not a primary key of table 2. The following does not work:

ALTER TABLE IS3.AUCTION_SUPER_CATEGORIES
ADD CONSTRAINT code_not_fk
check(code not in
(select code from IS3.AUCTION_CATEGORIES
where auction_id=auction_id))

Is there any way other than a trigger (or program checks) to write this?

What is the reason that you don't want to use trigger?
DB2 CHECK constraint can't be refer to columns of another table.
I feel that using trigger is natural and easy for this problem.

[Knut Stolze]

Tonkuma wrote:

On Mar 9, 8:28 am, Bob Stearns <[e-mail]:

>For good and sufficient reasons I wish to insure that a primary key of
table 1 is not a primary key of table 2. The following does not work:

ALTER TABLE IS3.AUCTION_SUPER_CATEGORIES
ADD CONSTRAINT code_not_fk
check(code not in
(select code from IS3.AUCTION_CATEGORIES
where auction_id=auction_id))

Is there any way other than a trigger (or program checks) to write this?

What is the reason that you don't want to use trigger?
DB2 CHECK constraint can't be refer to columns of another table.
I feel that using trigger is natural and easy for this problem.

Moreover, a CHECK constraint can operate on a single (the current) row only
(plus constants). It is more a "row check constraint" instead of a "table
check constraint".

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany