keys in DB2
Earlier we have made basic tables only, to make an actual table in a production environment we have to use keys in DB2 and indexes.
UNIQUE KEY
IF we are sure that a particular column contains unique data that we can declare that column as a UNIQUE KEY. UNIQUE KEY holds unique value.
- We declare this for columns that are supposed to contain unique data records.
- It is a key which has the limitation of holding unique values.
- ’NOT NULL’ constraints are always declared with unique KEY.
- Multiple unique keys can be present in a table.
Syntax:
CREATE TABLE TABLE-NAME9(COL1 DATA TYPE NOT NULL UNIQUE, COL2 DATA TYPE…)
Here, We are using COL1 UNIQUE KEY.
Example
CREATE TABLE BANK_EMPLOYEE(EMP_ID NUMERIC NOT NULL UNIQUE;
In this EMP_ID will always contain unique records.
UNIQUE INDEX
In order to disallow duplicates, we use a UNIQUE INDEX. We use this to retrieve the records faster, and it provides a way to access the records.
Syntax:
CREATE UNIQUE INDEX NAME
ON TABLE-NAME (COLUMN-NAME);
Example
CREATE UNIQUE INDEX U_IDX_BANK_EMP ON BANK_EMPLOYEE(EMP_ID);
Here, we are creating a unique index for EMP_ID and unique index name is “U_IDX_BANK_EMP”.
Primary Key
A primary key is an optional key, it is not the mandatory key. We use this key to identify the primary keys in the table.
- A primary key is a special type of UNIQUE KEY and is optional, NULLS are not allowed for Primary key.
- All the columns/s which are part of the Primary key must be defined with ‘NOT NULL’ constraint.
- An index created on primary key is called as Primary INDEX.
- There will be only 1 primary Key in a table.
Syntax:
PRIMARY KEY(COL1, COL2, COL3,……..).
Example
EMP_ADDR VARCHAR (50) NOT NULL, CONSTRAINTS PK_BANK_EMPLOYEE;
PRIMARY KEY(EMP_ID, EMP_SECTOR) IN DBMATE1.TSMATEKS;
WHAT IS NULL, NOT NULL AND DEFAULT
NULL/ NOT NULL and DEFAULT can be defined at the field level. If NOT NULL is not defined by default NULL is enabled.
NULL – NULL for a column specifies that values are allowed in that particular column.
NOT NULL – If NOT NULL defines column then it will not allow any values in that particular column
DEFAULT – DDL Statement is optional phrase and we use it to provide a DEFAULT value in case the value of a particular column is not moved explicitly.
Syntax:
DEFAULT ‘DEFAULT-VALUE’
DEFAULT DEFAULT-VALUE
Example
CREATE TABLE EMP_DEPT(DEPT_ID INTEGER NOT NULL UNIQUE;
DEPT_NAME CHAR(3) NOT NULL UNIQUE;
DEPT_CODE CHAR(2), DEPT_ADDR VARCHAR(50) NOT NULL
DEFAULT ‘DUMMY STREET 123’) IN DBMATE1.TSMATEKS;
Here, we have defined DEPT_ID, as not null unique, because DEPT_id is unique for everyone, it can’t be the same for two people. As we can see in DEPT_CODE I have not given anything, so by default, it will take NULL values. For DEPT ADDR it is NOT NULL, so if there is no value than by default it will take ‘DUMMY STREET 123’.
REFERENTIAL INTEGRITY
It is a way to keep the data consistency between the multiple tables if those tables are related to each other. REFERENTIAL INTEGRITY is a means by which data should be consistent between multiple tables.
IF there are two tables, then you can link one primary key column to another table Foreign key, to create a link in both tables, if the table is related to each other and column record is matching.
FOREIGN KEY
It is a way to keep the data consistency between the multiple tables if those tables are related to each other. REFERENTIAL INTEGRITY is a means by which data should be consistent between multiple tables.
IF there are two tables, then you can link one primary key column to another table Foreign key, to
Create a link in both tables, if the table is related to each other and column record is matching.
CASCADE: When a record is deleted from a parent table, the related record in the child table is automatically deleted.
RESTRICT: If we try to delete the record from the parent table, the operation will be denied. First, we will have to delete from parent table than from the child table.
And this is the default option while defining Referential integrity.
SET NULL: When the reference record is deleted in the main table, the related record in the child table is set with a NULL value.
Syntax:
DEFAULT ‘DEFAULT-VALUE’
DEFAULT DEFAULT-VALUE