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));
  

Data Types in SQL

Data Types :


Datetime Data Types :

• The TIMESTAMP data type is an extension of the DATE data type.
• It stores the year, month, and day of the DATE data type plus hour, minute, and second values as well as the fractional second value.
• You can optionally specify the time zone.

• TIMESTAMP[(fractional_seconds_precision)]
• TIMESTAMP[(fractional_seconds_precision)] WITH TIME ZONE