Cannot add Foreign Key on tables in DashDB / DB2 o

问题:

When I create a table in DashDB (DB2) on Bluemix like this:

CREATE TABLE DEPARTMENT (
    depname CHAR (10) UNIQUE NOT NULL ,
    phone   INTEGER
  ) ;
ALTER TABLE DEPARTMENT ADD CONSTRAINT DEPARTMENT_PK PRIMARY KEY ( depname ) ;

CREATE TABLE EMPLOYEE (
    "EmpNr"          NUMERIC (3) UNIQUE NOT NULL ,
    empname         CHAR (20) ,
    depname         CHAR (10) ,
    EMPLOYEE2_title CHAR (20)
  );
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY ( "EmpNr" ) ;

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_DEPARTMENT_FK FOREIGN KEY (depname ) REFERENCES DEPARTMENT ( depname ) ;

Bluemix disallows adding a Foreign Key constraint for this table type.

回答1:

When you look at the documentation for dashDB (not DB2) you will notice that foreign keys can be created. However, a table by default is created column-organized. Only non-enforced referential constraints are supported. In your example you would need to add NOT ENFORCED to your statement:

ALTER TABLE EMPLOYEE 
 ADD CONSTRAINT EMPLOYEE_DEPARTMENT_FK FOREIGN KEY (depname ) 
 REFERENCES DEPARTMENT ( depname ) NOT ENFORCED;

回答2:

By default on CREATE a DashDB table on Bluemix is 'organized by column'... https://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0060592.html

This will also disallow adding Foreign Key constraints for this table type.

To add FKs add ORGANIZE BY ROW to your CREATE TABLE statement:

CREATE TABLE DEPARTMENT (
    depname CHAR (10) UNIQUE NOT NULL ,
    phone   INTEGER
  ) ORGANIZE BY ROW;

ALTER TABLE DEPARTMENT 
 ADD CONSTRAINT DEPARTMENT_PK PRIMARY KEY ( depname ) ;

CREATE TABLE EMPLOYEE (
    "EmpNr"          NUMERIC (3) UNIQUE NOT NULL ,
    empname         CHAR (20) ,
    depname         CHAR (10) ,
    EMPLOYEE2_title CHAR (20)
  ) ORGANIZE BY ROW;

ALTER TABLE EMPLOYEE 
 ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY ( "EmpNr" ) ;

ALTER TABLE EMPLOYEE 
 ADD CONSTRAINT EMPLOYEE_DEPARTMENT_FK FOREIGN KEY (depname )
 REFERENCES DEPARTMENT ( depname ) ;