473,837 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Foreign constraints DB2 UDB v8.1.7 Linux

Why is the following constraint invalid? I want to make sure that every
row in IS3.ANIMALS_PRI V_INDEXES matches one of those in
IS3.table_var_d efn 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_PRI V_INDEXES
ADD CONSTRAINT
FOREIGN KEY(3, variable_id)
REFERENCES IS3.table_var_d efn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

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

ALTER TABLE IS3.ANIMALS_PRI V_LABELS
ADD CONSTRAINT
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_d efn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
Nov 12 '05 #1
13 3707
"Bob Stearns" <rs**********@c harter.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_PRI V_INDEXES matches one of those in IS3.table_var_d efn
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_PRI V_INDEXES
ADD CONSTRAINT
FOREIGN KEY(3, variable_id)
REFERENCES IS3.table_var_d efn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

ALTER TABLE IS3.ANIMALS_PRI V_INDEXES
ADD CONSTRAINT
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_d efn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
Nov 12 '05 #2
Add constraint name:

ALTER TABLE IS3.ANIMALS_PRI V_LABELS
ADD CONSTRAINT xxxxxx
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_d efn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

Nov 12 '05 #3
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_PRI V_INDEXES
ADD CONSTRAINT <constraint-name>
FOREIGN KEY(<column-name>, variable_id)
REFERENCES IS3.table_var_d efn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

Nov 12 '05 #4
Mark A wrote:
"Bob Stearns" <rs**********@c harter.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_PRI V_INDEXES matches one of those in IS3.table_var_d efn
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_PRI V_INDEXES
ADD CONSTRAINT
FOREIGN KEY(3, variable_id)
REFERENCES IS3.table_var_d efn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION


ALTER TABLE IS3.ANIMALS_PRI V_INDEXES
ADD CONSTRAINT
FOREIGN KEY(table_id, variable_id)
REFERENCES IS3.table_var_d efn(table_id, variable_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION

table_id is deliberately not a column of IS3.ANIMALS_PRI V_INDEXES, since
it is an unneeded, constant column.
Nov 12 '05 #5
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_PRI V_INDEXES
ADD CONSTRAINT <constraint-name>
FOREIGN KEY(<column-name>, variable_id)
REFERENCES IS3.table_var_d efn(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
"Bob Stearns" <rs**********@c harter.net> wrote in message
news:AV******** *******@fe06.lg a...
table_id is deliberately not a column of IS3.ANIMALS_PRI V_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
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_PRI V_INDEXES.

Nov 12 '05 #8
For example:
CREATE TRIGGER ANIMALS_PRIV_IX _I
NO CASCADE BEFORE INSERT ON IS3.ANIMALS_PRI V_INDEXES
REFERENCING NEW AS n
FOR EACH ROW
WHEN (NOT EXISTS
(SELECT *
FROM IS3.table_var_d efn 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_PRI V_INDEXES
REFERENCING NEW AS n
FOR EACH ROW
WHEN (NOT EXISTS
(SELECT *
FROM IS3.table_var_d efn vd
WHERE vd.table_id = 3
AND vd.variable_id = n.variable_id
)
) SIGNAL SQLSTATE 'APUPD';

Nov 12 '05 #9
Tonkuma wrote:
For example:
CREATE TRIGGER ANIMALS_PRIV_IX _I
NO CASCADE BEFORE INSERT ON IS3.ANIMALS_PRI V_INDEXES
REFERENCING NEW AS n
FOR EACH ROW
WHEN (NOT EXISTS
(SELECT *
FROM IS3.table_var_d efn 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_PRI V_INDEXES
REFERENCING NEW AS n
FOR EACH ROW
WHEN (NOT EXISTS
(SELECT *
FROM IS3.table_var_d efn 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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5282
by: Jason Madison | last post by:
We sometimes get very large databases that we want to cut down to use for testing. The information is all related to a central accounts table. The way I thought of doing this is to grab all the foreign constraints and turn them into cascade delete constraints, then delete as many accounts as I want. After this I will restore the constraints back to their original state.
0
1418
by: Scott Ribe | last post by:
I've got a problem which I think may be a bug in Postgres, but I wonder if I'm missing something. Two tables, A & B have foreign key relations to each other. A 3rd table C, inherits from A. A stored procedure updates a row in C, adds a row each in B & C. I get an integrity violation. All the foreign keys are deferrable, and the stored procedure is called from within a transaction with constraints deferred. (And the foreign keys do refer to...
8
3540
by: wespvp | last post by:
I am using PostgreSQL 7.4.1 on RedHat 7.2. The query I am executing is something like (I replaced all the return values with 'count'): db=> explain select count(*) from messages m join (select * from message_recipients r join addresses a on a.Address_Key=r.Recipient where a.Address='lra.edi@edi.cma-cgm.com') as foo on (m.Message_Key=foo.Message_Key AND (m.Message_Date >= '29-MAR-04') AND (m.Message_Date <= '31-MAR-04...
3
10093
by: David Parker | last post by:
I would like to be able to truncate all of the tables in a schema without worrying about FK constraints. I tried issuing a "SET CONSTRAINTS ALL DEFERRED" before truncating, but I still get constraint errors. Is there a way todo something like: 1) disable all constraints 2) truncate all tables 3) re-enable all constraints ? In the slony project there is a procedure "altertableforreplication" that appears to do 1), but since it is...
11
10169
by: FreeToGolfAndSki | last post by:
Hi, I have an Orders Table that has Employee1 and Employee2 (one is the sales rep the other is the telemarketing rep) Both of these fields need to cascade update against the Employees table. I can't seem to create the desired relationship in a Diagram and I'm not sure how best to set this up. Any ideas? Thanks in advance...
7
3603
by: BWill | last post by:
Hi, I'm writing a file browser, but I'm not sure how I could go about detecting the drives available on windows and linux systems (preferably using the standard modules if possible). I guess I could just try to list root on each letter of the alphabet for windows and see if it works, but that seems crude; besides, I want to know what kind of drive each drive/partition is (i.e. is it local and is it a harddrive or optical drive). Thanks,
0
2700
by: mrhodes02 | last post by:
I have two simple tables Employees (primary key table) Empid, empname,title MgrXrf (foreign key table) MgrId,Empid MgrID & Empid in MgrXrf have a foreign contstrant to EmpID in Employees. That works fine. I don' want to add a record to MgrXRF unless both field values exist in Employees. Problem is I also want to set the delete rule to cascade on both fields, so that if I delete an employee their record is deleted from MgrXRF no...
0
2162
by: okonita | last post by:
DB2v8.2 LUW. Unable to drop Foreign Key constraints...although DROP constraints returns undefined name. Am I missing somrthing here? Hi all, I have a perplexing DB2 Drop command situation. Environment I have tried severally to drop Foreign Key constraints on a set of tables and each time I get a SQL0204N "SQL071030104307060" is an undefined name. ". However, when I go back to look at the table or load data, I get a constraint error...
4
22013
by: Bobby Edward | last post by:
I have an xsd dataset. I created a simple query called GetDataByUserId. I can preview the data fine! I created a very simple BLL function that calls it and returns a datatable. When I run the code I get.... ======================= Server Error in '/MyCompanyMyProject' Application.
0
9851
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9695
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10902
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10583
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9420
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5680
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4481
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3128
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.