Adding Constraints in SQL

NOT NULL Constraint :

• Ensures that null values are not permitted for the column
• CREATE TABLE emp(employee_id numeric(6)CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name varchar(20) NOT NULL,...);

UNIQUE Constraint :

• Unique constraint allow only unique values to be inserted in the column.
• No duplicates allowed e.g emailIDs
• CREATE TABLE emp(employee_id numeric(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY, first_name varchar(20) NOT NULL, Email_id varchar(20) UNIQUE,...);

PRIMARY KEY Constraint :

• Don’t allow duplicate values
  
• Don’t allow null values
• Only one primary key can be created per table
• CREATE TABLE emp(employee_id numeric(6) CONSTRAINT emp_emp_id_pk RIMARY KEY, first_name varchar(20),...);

FOREIGN KEY Constraint :

• Defined at either the table level or the column level : continued in the next post
  

Including Constraints in SQL

Constraints :

• Constraints enforce rules at the table level.
• Constraints prevent the deletion of a table if there are dependencies.

The following constraint types are valid :

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK

Constraint Guidelines :

• You can name a constraint, or the PostgreSQL server generates a name automatically
• Create a constraint at either of the following times :
  • At the same time as the table is created
  • After the table has been created
• Define a constraint at the column or table level
• View a constraint in the data dictionary

Defining Constraints :

Syntax :

• CREATE TABLE [schema.]table (column datatype [DEFAULT expr] [column_constraint], ... [table_constraint][,...]);

Column-level constraint :

• column [CONSTRAINT constraint_name] constraint_type,
• CREATE TABLE emp(employee_id numeric(6) CONSTRAINT emp_emp_id_pk RIMARY KEY, first_name varchar(20), ...);
  

Table-level constraint :

• column,... [CONSTRAINT constraint_name] constraint_type (column, ...),
• CREATE TABLE emp(employee_id numeric(6), first_name varchar(20), ...
  job_id varchar(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY EMPLOYEE_ID));