Question

Describe the use of primary key and unique key constraints with example?

Answer 1

There are two Entity constraints:

1.Primary Key constraint

2. Unique Constraint

1. Primary Key constraint: It is use to avoid redundant/duplicate value entry within the row of specified column in table. It restricts null values too.

Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME

DATA_TYPE, COLUMN_NAME DATA_TYPE CONSTRAINT

CONSTRAINT_NAME PRIMARY KEY);

Example: SQL> CREATE TABLE EMP (ID NUMBER

(5)CONSTRAINT ID_PK PRIMARY KEY, NAME VARCHAR2

(10), SAL NUMBER (10));

2. Unique Constraint: The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. 

syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME

DATA_TYPE, COLUMN_NAME DATA_TYPE CONSTRAINT

CONSTRAINT_NAME UNIQUE);

Example: CREATE TABLE PERSONS (P_ID NUM CONSTRAINT

P_UK UNIQUE , FIRSTNAME VARCHAR(20), CITY

VARCHAR(20) );