DB2 - DDL Primary key

First Page
Next Page

DB2 DDL Primary key

A primary key of a table is a column or group of columns whose values uniquely identify every row in the table. Each table has one and only one primary key. A primary key is optional. However, it’s a good practice to have a primary key in every table. To define a primary key for a table, you use the PRIMARY KEY constraint.

Uniquely identified column which is used to identify the row uniquely is known as primary key. Primary key can be defined during the CREATE TABLE or ALTER TABLE. Primary keys definition is optional in CREATE TABLE or ALTER TABLE. Primary key is unique key and cannot contain NULL values. Primary key can be only one column or more than one column. The index on Primary key is called as Primary index. If primary key is created on the specific table, the primary index will be automatically created by DB2.

Primary key

SELECT NAME
FROM
SYSIBM.SYSCOLUMNS
WHERE TBNAME = 'table name'
AND   TBCREATOR = 'creator'
AND   KEYSEQ > 0
ORDER BY KEYSEQ ASC

Foreign Key

SELECT B.REFTBNAME AS PARENTTABLE,COLNAME
FROM
SYSIBM.SYSFOREIGNKEYS A,
SYSIBM.SYSRELS B
WHERE A.RELNAME = B.RELNAME
AND   B.TBNAME = 'table name'
AND   B.REFTBCREATOR = 'creator'
AND   B.REFTBCREATOR = A.CREATOR

What is DB2 DDL 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,……..).

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’.

What is a Primary Key (PK)?

If you’ve already worked with databases, then you could hardly miss the term – Primary Key (PK). And if you’re reading this series and use it to learn about databases, well, this article should give you a good overview of what the PK really is. Still, if you’re a database expert, maybe you’ll find something new or just refresh your knowledge. So, sit back, relax, and let’s dive into PKs.

We’re literally surrounded by PKs in the database world. But we mostly take them for granted. Before examples let’s go with a simplified definition of a PK, “Primary key is a value which is unique for every record in the table.”

And a rule – “Each table in the database should have a PK defined.

Primary Key Constraint is a type of key through which you can uniquely identify every tuple or a record in a table. Every table can have only one primary key but can have multiple candidate keys. Also, each primary key should be unique and must not contain any NULL values.

Primary keys are used along with the foreign keys to refer to various tables and form referential integrities. For Table A, a primary key can consist of single or multiple columns. Now that you know what is the primary key, next in this article on Primary Key in SQL, let us understand the rules of the primary key.

Rules for Primary Key

The rules of Primary Key are as follows -

  • All the values in the column chosen as the primary key must be unique.

  • No value in the primary key column can be NULL.

  • You cannot insert a new row with a pre-existing primary key.

  • Each and every table can have only one primary key.

First Page
Next Page