Foreign constraints DB2 UDB v8.1.7 Linux

[Bob Stearns]

Why is the following constraint invalid? I want to make sure that every
row in IS3.ANIMALS_PRIV_INDEXES matches one of those in
IS3.table_var_defn with part of the primary key fixed. Since this is
illegal with the following message, how do I achieve this result? The
last thing below is a working constraint from another table.

ALTER TABLE IS3.ANIMALS_PRIV_INDEXES
ADD CONSTRAINT
FOREIGN KEY(3, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: FOREIGN KEY;ADD
CONSTRAINT;NUMERIC
Message: An unexpected token "FOREIGN KEY" was found following "ADD
CONSTRAINT". Expected tokens may include: "NUMERIC".

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

[Mark A]

"Bob Stearns" <rs**********@charter.net> wrote in message
news:Nq*****************@fe03.lga...

Why is the following constraint invalid? I want to make sure that every
row in IS3.ANIMALS_PRIV_INDEXES matches one of those in IS3.table_var_defn
with part of the primary key fixed. Since this is illegal with the
following message, how do I achieve this result? The last thing below is a
working constraint from another table.

ALTER TABLE IS3.ANIMALS_PRIV_INDEXES
ADD CONSTRAINT
FOREIGN KEY(3, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

ALTER TABLE IS3.ANIMALS_PRIV_INDEXES
ADD CONSTRAINT
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

[Tonkuma]

Add constraint name:

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT xxxxxx
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

[Tonkuma]

I'm sorry. I referenced different ALTER statement in your post.
I think your ALTER statement have two problem.
1) constraint-name is required after CONSTRAINT keyword.
Though, you wrote

The last thing below is a working constraint from another table.

But, in my DB2 UDB V8 FP9a on Windows, I got following message, if no
constraint-name was specified.
SQL0104N An unexpected token "FOREIGN KEY" was found following "loyee
ADD CONSTRAINT". Expected tokens may include: "<col_type>".
SQLSTATE=42601

2) FOREIGN KEY can be specified only column name. You specified
constant number 3.

ALTER TABLE IS3.ANIMALS_PRIV_INDEXES
ADD CONSTRAINT <constraint-name>
FOREIGN KEY(<column-name>, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

[Bob Stearns]

Mark A wrote:

"Bob Stearns" <rs**********@charter.net> wrote in message
news:Nq*****************@fe03.lga...

Why is the following constraint invalid? I want to make sure that every
row in IS3.ANIMALS_PRIV_INDEXES matches one of those in IS3.table_var_defn
with part of the primary key fixed. Since this is illegal with the
following message, how do I achieve this result? The last thing below is a
working constraint from another table.

ALTER TABLE IS3.ANIMALS_PRIV_INDEXES
ADD CONSTRAINT
FOREIGN KEY(3, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

ALTER TABLE IS3.ANIMALS_PRIV_INDEXES
ADD CONSTRAINT
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

table_id is deliberately not a column of IS3.ANIMALS_PRIV_INDEXES, since
it is an unneeded, constant column.

[Bob Stearns]

Tonkuma wrote:

I'm sorry. I referenced different ALTER statement in your post.
I think your ALTER statement have two problem.
1) constraint-name is required after CONSTRAINT keyword.
Though, you wrote

The last thing below is a working constraint from another table.

But, in my DB2 UDB V8 FP9a on Windows, I got following message, if no
constraint-name was specified.
SQL0104N An unexpected token "FOREIGN KEY" was found following "loyee
ADD CONSTRAINT". Expected tokens may include: "<col_type>".
SQLSTATE=42601

2) FOREIGN KEY can be specified only column name. You specified
constant number 3.

ALTER TABLE IS3.ANIMALS_PRIV_INDEXES
ADD CONSTRAINT <constraint-name>
FOREIGN KEY(<column-name>, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

I did miss the constraint name, which made the error message bad. But my
original question stands: How can I have an FK relationship where part
of the FK is constant without having a redundant, extraneous, constant
column in my table?

[Mark A]

"Bob Stearns" <rs**********@charter.net> wrote in message
news:AV***************@fe06.lga...

table_id is deliberately not a column of IS3.ANIMALS_PRIV_INDEXES, since
it is an unneeded, constant column.

You cannot put a constant in foreign key definition. A foreign key must be
composed of column names (not constants) that are Primary Keys or Unique
Keys on a parent table.

I really don't know what you are trying to due, but maybe you need to create
a regular constraint where one of the columns is equal to the constant 3,
and not a foreign key.

[Tonkuma]

If you don't want to add a column.
Only idea I could thoght was to create triggers for insert and update
on IS3.ANIMALS_PRIV_INDEXES.

[Tonkuma]

For example:
CREATE TRIGGER ANIMALS_PRIV_IX_I
NO CASCADE BEFORE INSERT ON IS3.ANIMALS_PRIV_INDEXES
REFERENCING NEW AS n
FOR EACH ROW
WHEN (NOT EXISTS
(SELECT *
FROM IS3.table_var_defn vd
WHERE vd.table_id = 3
AND vd.variable_id = n.variable_id
)
) SIGNAL SQLSTATE 'APIST';

CREATE TRIGGER ANIMALS_PRIV_IX_U
NO CASCADE BEFORE UPDATE OF variable_id ON IS3.ANIMALS_PRIV_INDEXES
REFERENCING NEW AS n
FOR EACH ROW
WHEN (NOT EXISTS
(SELECT *
FROM IS3.table_var_defn vd
WHERE vd.table_id = 3
AND vd.variable_id = n.variable_id
)
) SIGNAL SQLSTATE 'APUPD';

[Bob Stearns]

Tonkuma wrote:

For example:
CREATE TRIGGER ANIMALS_PRIV_IX_I
NO CASCADE BEFORE INSERT ON IS3.ANIMALS_PRIV_INDEXES
REFERENCING NEW AS n
FOR EACH ROW
WHEN (NOT EXISTS
(SELECT *
FROM IS3.table_var_defn vd
WHERE vd.table_id = 3
AND vd.variable_id = n.variable_id
)
) SIGNAL SQLSTATE 'APIST';

CREATE TRIGGER ANIMALS_PRIV_IX_U
NO CASCADE BEFORE UPDATE OF variable_id ON IS3.ANIMALS_PRIV_INDEXES
REFERENCING NEW AS n
FOR EACH ROW
WHEN (NOT EXISTS
(SELECT *
FROM IS3.table_var_defn vd
WHERE vd.table_id = 3
AND vd.variable_id = n.variable_id
)
) SIGNAL SQLSTATE 'APUPD';

Thank you for the example.

It seems a long way to go for what appears to me to be a simple
declarative constraint.

[mirof007]

Bob Stearns wrote:
....

It seems a long way to go for what appears to me to be a simple
declarative constraint.

Bob, foreign keys are used to enforce relationships between table
columns as prescribed by the logical model of your data. If you want to
restrict a value of a particular table column to a single constant, I
would suggest using a check constraint, which is best suitable for
enforcing domain constraints in your model.

So you could consider changing your constraint definition to the
following two:

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr1
FOREIGN KEY(variable_id)
REFERENCES IS3.table_var_defn(variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr2 CHECK(table_id = 3);

Hope this helps,
Miro

[Bob Stearns]

mirof007 wrote:

Bob Stearns wrote:
...

It seems a long way to go for what appears to me to be a simple
declarative constraint.

Bob, foreign keys are used to enforce relationships between table
columns as prescribed by the logical model of your data. If you want to
restrict a value of a particular table column to a single constant, I
would suggest using a check constraint, which is best suitable for
enforcing domain constraints in your model.

So you could consider changing your constraint definition to the
following two:

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr1
FOREIGN KEY(variable_id)
REFERENCES IS3.table_var_defn(variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr2 CHECK(table_id = 3);

Hope this helps,
Miro

But that has different semantics than the constraint I want. These two
constraints only guarantee that variable_id in animals_priv_labels
occurs somewhere in table_var_defn, not in that subset with table_id=3.

[Knut Stolze]

Bob Stearns wrote:

mirof007 wrote:

Bob Stearns wrote:
...

It seems a long way to go for what appears to me to be a simple
declarative constraint.

Bob, foreign keys are used to enforce relationships between table
columns as prescribed by the logical model of your data. If you want to
restrict a value of a particular table column to a single constant, I
would suggest using a check constraint, which is best suitable for
enforcing domain constraints in your model.

So you could consider changing your constraint definition to the
following two:

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr1
FOREIGN KEY(variable_id)
REFERENCES IS3.table_var_defn(variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr2 CHECK(table_id = 3);

But that has different semantics than the constraint I want. These two
constraints only guarantee that variable_id in animals_priv_labels
occurs somewhere in table_var_defn, not in that subset with table_id=3.

Then just switch the definitions around:

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr2 CHECK(table_id = 3);

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr1
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

You have to stick with the additional column, though.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Bob Stearns]

Knut Stolze wrote:

Bob Stearns wrote:

mirof007 wrote:

Bob Stearns wrote:
...
It seems a long way to go for what appears to me to be a simple
declarative constraint.
Bob, foreign keys are used to enforce relationships between table
columns as prescribed by the logical model of your data. If you want to
restrict a value of a particular table column to a single constant, I
would suggest using a check constraint, which is best suitable for
enforcing domain constraints in your model.

So you could consider changing your constraint definition to the
following two:

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr1
FOREIGN KEY(variable_id)
REFERENCES IS3.table_var_defn(variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr2 CHECK(table_id = 3);

But that has different semantics than the constraint I want. These two
constraints only guarantee that variable_id in animals_priv_labels
occurs somewhere in table_var_defn, not in that subset with table_id=3.

Then just switch the definitions around:

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr2 CHECK(table_id = 3);

ALTER TABLE IS3.ANIMALS_PRIV_LABELS
ADD CONSTRAINT cnstr1
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_defn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

You have to stick with the additional column, though.

That is what I was objecting to all along. It should be possible to put
constants in the FOREIGN KEY part of the constraint. The presence of a
constant value column in the table offends my sense of redundancy. In my
case, it is a small thing, but in other cases, I could see it adding
significantly to the overall table size.