The sample DDL for Alter Table ... Add Foreign Key
The documentation only provides the syntax but does not provide any sample DDL for this case.
I am modifying some DB2 queries to Cache SQL but always meet some errors, please provide a sample query for this case.
Thanks.
Comments
What error is "some" ?
Detailed description on ADD FOREIGN KEY
Look especially on
Go to the Management Portal, select [Home] > [Configuration] > [General SQL Settings]. View the current setting of
Allow DDL ADD Foreign Key Constraint When Foreign Key Exists.
I got below working but I doubt this is really what I want. Although the query is accepted, I don't see how could I specify constraint name and
real key property simultaneously.
[SQL]SAMPLES>>ALTER TABLE TPCD.NATION ADD FOREIGN KEY (N_REGIONKEY) references TPCD.REGION(R_REGIONKEY);
87. ALTER TABLE TPCD.NATION ADD FOREIGN KEY (N_REGIONKEY) references TPCD.REGION(R_REGIONKEY);
0 Rows Affected
Below is what I want:
ALTER TABLE TPCD.NATION ADD FOREIGN KEY NATION_FK2 (N_REGIONKEY2) references TPCD.REGION(R_REGIONKEY);
"NATION_FK2" is the constraint name and "N_REGIONKEY2" is the key property pointing to TPCD.REGION.
Take another sample from your documentation:
PLEASE provide a working ALTER TABLE DDL which specifies both "StateFKey" and "State" in the query .
Class MyApp.Company Extends %Persistent
{
Property State As %String;
ForeignKey StateFKey(State) References StateTable(StateKey);
}and your class should have class parameter DDL Allowed
Ok. this was clear to understand.
You require 2 ALTER TABLE
1) add column N_REGIONKEY2 to 'host' your foreign key
ALTER TABLE PCD.NATION
ADD COLUMN N_REGIONKEY2 INTEGER
2) add foreign key
ALTER TABLE PCD.NATION
ADD CONSTRAINT NATION_FK
FOREIGN KEY (N_REGIONKEY2) REFERENCES TPCD.REGION(R_REGIONKEY);