ADD Constraint in DDL tables

Constraints are one of the important concepts in the DDL tables.

In this article, we will go through how one can add constraints in the DDL files.

Constraints Types

There are three types of constraints that can be added to the DDL tables

Primary Key
Foreign Key
Unique Key

In DDS file constraints can be added in the file through the ADDPFCST command.

But, in the DDL table ALTER TABLE with ADD CONSTRAINT is used in the SQL script to add constraints.

ALTER TABLE  TableName  ADD CONSTRAINT ConstraintName ConstraintType(FieldName)

Primary Key

A primary key can be a single column or group of columns that are used to identify the unique row in the table.

Primary Key on a single column

ALTER TABLE TableName ADD CONSTRAINT ConstraintName PRIMARY KEY(FieldName);

Primary Key on multiple columns (Composite Key)

ALTER TABLE TableName ADD CONSTRAINT ConstraintName PRIMARY KEY(FieldName1,FieldName2);

DDL Script on Primary Key

CREATE TABLE DDL101(                                            
  FLD1 CHAR(10) NOT NULL WITH DEFAULT,                          
  FLD2 DECIMAL(2) NOT NULL WITH DEFAULT);                       
                                                                
ALTER TABLE DDL101 ADD CONSTRAINT CST3 PRIMARY KEY(FLD1, FLD2);

DSPFD on Primary Key Table

Unique Key

A Unique key can be a single column or group of columns that are used to identify the unique row in the table.

But, it's not the same as the primary key as it can accept NULL values and a table can have multiple unique keys but in the case of the primary key it won't accept NULL value also there can be only one primary key per table

Unique Key on a single column

ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(FieldName);

Unique Key on multiple columns (Composite Key)

ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(FieldName1,FieldName2);

DDL Script on Unique Key

CREATE TABLE DDL10(                                            
  FLD1 CHAR(10) NOT NULL WITH DEFAULT,                          
  FLD2 DECIMAL(2) NOT NULL WITH DEFAULT);                       
                                                                
ALTER TABLE DDL10 ADD CONSTRAINT CST2 UNIQUE(FLD1, FLD2);

DSPFD on Unique Key Table

Foreign Key

A foreign key can be a single column or group of columns that act as the link between two tables through the primary key of another table and foreign key relations.

Foreign Key Syntax

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName FOREIGN KEY (ForeignKeyFieldName) REFERENCES PrimaryKeyFileName(PrimaryKeyFieldName);

Read also :

AS400 and SQL Tricks