New CONSTRAINT clause in SQL TABLE definitions.

Document ID : KB000010940
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

This document describes the nature of the indexes generated automatically as a result of the new ADD CONSTRAINT clause of the CREATE TABLE syntax.

Background:

It has always been possible to define referential constraints as SQL database entities independent of any TABLE definition.

As of r19.0 Incremental Release 3 (RO89206) it has been possible to define referential constraints – using type FOREIGN KEY - as a clause of the CREATE TABLE statement of the referencing table of the constraint.

A new constraint type was also added to the CREATE TABLE syntax for the purpose of defining uniqueness in a column value.

 

All of these new constraint definitions were added to the ALTER TABLE syntax in Incremental Release 4 (RO90312).

Environment:
CA-IDMS r19.0.
Instructions:

You can now define a column in a table as being a primary key simply be defining a CONSTRAINT of type PRIMARY KEY in the CREATE TABLE statement. For example:

SET SESSION CURRENT SCHEMA SQL;
CREATE TABLE DEPARTMENT
  ( DEPT_ID                          UNSIGNED NUMERIC(4) NOT NULL,
    DEPT_HEAD_ID                     UNSIGNED NUMERIC(4),
    DIV_CODE                         CHARACTER(3) NOT NULL,
    DEPT_NAME                        CHARACTER(40) NOT NULL,
    CONSTRAINT DEPT_ID_PK PRIMARY KEY ( DEPT_ID )
  );

By displaying back the table, you can see the name of the automatically generated index which supports the constraint:

     CREATE TABLE SQL.DEPARTMENT
       ( DEPT_ID                          UNSIGNED NUMERIC(4) NOT NULL,
         DEPT_HEAD_ID                     UNSIGNED NUMERIC(4),
         DIV_CODE                         CHARACTER(3) NOT NULL,
         DEPT_NAME                        CHARACTER(40) NOT NULL,
         CONSTRAINT DEPT_ID_PK PRIMARY KEY
             ( DEPT_ID )
       )
         IN SQLWORK.SQL-AREA-1
*+       NO DEFAULT INDEX
*+       TABLE ID 1045
*+       DEPT_ID_PK UNIQUE INDEX ON ( DEPT_ID )
         ;

The index name can be used in the PRINT INDEX utility.

For example, if we add one row:

INSERT INTO DEPARTMENT VALUES (1, 1, 'ABC', 'ALPHABET');

We can then run a PRINT INDEX:

PRINT INDEX DEPT_ID_PK DBNAME APPLDB TABLE SQL.DEPARTMENT;
*+ Status = 0        SQLSTATE = 00000
*+ SET=DEPT_ID_PK
*+      OWNER=SR7              PAGE GROUP=0     RECORDS PER PAGE=255
*+       ODBK=03D4A701 SR8 N03D4A903 SR8 P03D4A903 ASC CUSH=12  SYM TKL=3
*+ UNCM
*+     MEMBER=DEPARTMENT       PAGE GROUP=0     RECORDS PER PAGE=255
*+ L0   03D4A903  NUME=1                 U=FFFFFFFF N=03D4A701 P=03D4A701
*+       RECL=56    SPA=4088
*+               03D47A31 0001
*+ TOTAL SR8=1

You can also define your own index for this purpose, for example if you would like to use specific physical implementation values such as IBC.
CA-IDMS will recognize that it can be used and will drop the automatically generated index:

CREATE UNIQUE INDEX NDX_DEPT_ID
    ON DEPARTMENT
        ( DEPT_ID ASC )
    UNCOMPRESSED
    IN SQLWORK.SQL-AREA-1
    INDEX BLOCK CONTAINS 20 KEYS DISPLACEMENT IS 4 PAGES
    ;
*+ Status = 0        SQLSTATE = 00000

Another DISPLAY of the table shows the new index and not the old one:

     CREATE TABLE SQL.DEPARTMENT
       ( DEPT_ID                          UNSIGNED NUMERIC(4) NOT NULL,
         DEPT_HEAD_ID                     UNSIGNED NUMERIC(4),
         DIV_CODE                         CHARACTER(3) NOT NULL,
         DEPT_NAME                        CHARACTER(40) NOT NULL,
         CONSTRAINT NDX_DEPT_ID PRIMARY KEY
             ( DEPT_ID )
       )
         IN SQLWORK.SQL-AREA-1
*+       NO DEFAULT INDEX
*+       TABLE ID 1045
*+       NDX_DEPT_ID UNIQUE INDEX ON ( DEPT_ID )
         ;

It is also possible to define a constraint of type UNIQUE which acts the same as a PRIMARY KEY constraint:

ALTER TABLE DEPARTMENT
  ADD CONSTRAINT DEPT_HEAD_ID_UNQ UNIQUE
        ( DEPT_HEAD_ID )
    ;

Now a DISPLAY of the table will show both constraints and supporting indexes:

     CREATE TABLE SQL.DEPARTMENT
       ( DEPT_ID                          UNSIGNED NUMERIC(4) NOT NULL,
         DEPT_HEAD_ID                     UNSIGNED NUMERIC(4),
         DIV_CODE                         CHARACTER(3) NOT NULL,
         DEPT_NAME                        CHARACTER(40) NOT NULL,
         CONSTRAINT DEPT_ID_PK PRIMARY KEY
             ( DEPT_ID ),
         CONSTRAINT DEPT_HEAD_ID_UNQ UNIQUE
             ( DEPT_HEAD_ID )
       )
         IN SQLWORK.SQL-AREA-1
*+       NO DEFAULT INDEX
*+       TABLE ID 1045
*+       DEPT_ID_PK UNIQUE INDEX ON ( DEPT_ID )
*+       DEPT_HEAD_ID_UNQ UNIQUE INDEX ON ( DEPT_HEAD_ID )
         ;

The only difference between a UNIQUE and a PRIMARY KEY constraint is that there can only be one PRIMARY KEY constraint on one table.
Note that this fails:

ALTER TABLE DEPARTMENT
  ADD CONSTRAINT DIV_CODE_PK PRIMARY KEY
        ( DIV_CODE )
    ;
*+ Status = -4       SQLSTATE = 42000        Messages follow:
*+ DB002077 T80 C-4M6004: Multiple Primary Keys specified

But this works:

ALTER TABLE DEPARTMENT
  ADD CONSTRAINT DIV_CODE_UNQ UNIQUE
        ( DIV_CODE )
    ;
*+ Status = 0        SQLSTATE = 00000

This new syntax can also be used to define FOREIGN KEY constraints with the CREATE/ALTER TABLE syntax.
As a result of such syntax, a supporting index is defined on the referencing table.
Note that for a foreign key constraint to exist, there must also be something defined on the referenced table to enforce uniqueness on the primary key column(s).
This can be a unique calc key, unique index, or a UNIQUE or PRIMARY KEY constraint which will define such an index.
The definition of a FOREIGN KEY constraint generates only the supporting index on the referencing table.
The DBA must define the calc key, index or constraint on the referenced table in order to support the FOREIGN KEY constraint.

For example, consider that we start again with the following two table definitions:

CREATE TABLE DEPARTMENT
  ( DEPT_ID                          UNSIGNED NUMERIC(4) NOT NULL,
    DEPT_HEAD_ID                     UNSIGNED NUMERIC(4),
    DIV_CODE                         CHARACTER(3) NOT NULL,
    DEPT_NAME                        CHARACTER(40) NOT NULL
  );

CREATE TABLE EMPLOYEE
  ( EMP_ID                           UNSIGNED NUMERIC(4) NOT NULL,
    LAST_NAME                        CHARACTER(15) NOT NULL,
    FIRST_NAME                       CHARACTER(10) NOT NULL,
    EMP_DEPT_ID                      UNSIGNED NUMERIC(4) NOT NULL
  );

We may try to define a FOREIGN KEY constraint between the two tables:

ALTER TABLE EMPLOYEE
  ADD CONSTRAINT EMP_DEPT_ID_FK FOREIGN KEY ( EMP_DEPT_ID )
        REFERENCES DEPARTMENT ( DEPT_ID );
*+ Status = -4       SQLSTATE = 42609        Messages follow:
*+ DB002103 T109 C-4M6004: Referenced table key not unique

The DB002103 message is returned because there is nothing defined on the DEPARTMENT table that makes DEPT_ID unique.
If I then define such a constraint on the DEPARTMENT table:

ALTER TABLE DEPARTMENT
  ADD CONSTRAINT DEPT_ID_PK PRIMARY KEY
        ( DEPT_ID )
    ;

Then the ADD CONSTRAINT works:

ALTER TABLE EMPLOYEE
  ADD CONSTRAINT EMP_DEPT_ID_FK FOREIGN KEY ( EMP_DEPT_ID )
        REFERENCES DEPARTMENT ( DEPT_ID );
*+ Status = 0        SQLSTATE = 00000

In this case, the name of the generated index is a little more cryptic:

     CREATE TABLE SQL.EMPLOYEE
       ( EMP_ID                           UNSIGNED NUMERIC(4) NOT NULL,
         LAST_NAME                        CHARACTER(15) NOT NULL,
         FIRST_NAME                       CHARACTER(10) NOT NULL,
         EMP_DEPT_ID                      UNSIGNED NUMERIC(4) NOT NULL
       )
         IN SQLWORK.SQL-AREA-1
*+       DEFAULT INDEX ON DBKEY
*+       TABLE ID 1048
*+       IDX719571741264139 INDEX ON ( EMP_DEPT_ID )
*+       EMP_DEPT_ID_FK CONSTRAINT FOREIGN KEY ( EMP_DEPT_ID )
*+           REFERENCES SQL.DEPARTMENT ( DEPT_ID )
         ;

It can still be used in PRINT INDEX.

Add some data:

INSERT INTO DEPARTMENT VALUES (1,1,'DIV','NAME');
INSERT INTO EMPLOYEE VALUES (1,'DUCK','DONALD',1);

And …

 

PRINT INDEX IDX719571741264139 DBNAME APPLDB TABLE SQL.DEPARTMENT;
*+ Status = 0        SQLSTATE = 00000
*+ SET=IDX7195717412641
*+      OWNER=SR7              PAGE GROUP=0     RECORDS PER PAGE=255
*+       ODBK=03D48902 SR8 N03D48B05 SR8 P03D48B05 ASC CUSH=12  SYM TKL=3
*+ UNCM
*+     MEMBER=EMPLOYEE         PAGE GROUP=0     RECORDS PER PAGE=255
*+ L0   03D48B05  NUME=1                 U=FFFFFFFF N=03D48902 P=03D48902
*+       RECL=56    SPA=3996
*+               03D49601 0001
*+ TOTAL SR8=1

Additional Information:

For more information, see the following CA IDMS DocOps page:

CREATE TABLE