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.

  • 0
  • 0
  • 338
  • 2
  • 2

Answers

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);

and your class should have class parameter DDL Allowed

Class PCD.NATION  Extends %Persistent [ DdlAllowed ]

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);

}