By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

Foreign constraints DB2 UDB v8.1.7 Linux

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/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
Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
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

Nov 12 '05 #4

P: n/a
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.
Nov 12 '05 #5

P: n/a
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?
Nov 12 '05 #6

P: n/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.
Nov 12 '05 #7

P: n/a
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.

Nov 12 '05 #8

P: n/a
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';

Nov 12 '05 #9

P: n/a
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.
Nov 12 '05 #10

P: n/a

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

Nov 12 '05 #11

P: n/a
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.
Nov 12 '05 #12

P: n/a
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
Nov 12 '05 #13

P: n/a
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.
Nov 12 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.