ADD Constraint in DDL tables

ADD Constraint in DDL tables
ADD Constraint in DDL tables, constratin in DDL, ddl constraint, constraint tyoe, primary key comstraint, unique key constraint, foreign key constraint, check cnstraint, as400,ibmi, db2, db2 for i sql, sql
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);

Related Post

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain