ALTER TABLE Statement

ALTER TABLE Statement :

• Use the ALTER TABLE statement to :
  – Add a new column
  – Modify an existing column
  – Define a default value for the new column
  – Drop a column

Dropping a Table :

• All data and structure in the table are deleted.
• Any pending transactions are committed.
• All indexes are dropped.
• All constraints are dropped.
• You cannot roll back the DROP TABLE statement.
• DROP TABLE dept30;
  

Creating a Table by Using a Subquery

Creating a Table by Using a Subquery :

• Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.
• Match the numeric of specified columns to the numeric of subquery columns.
• Define columns with column names and default values.
• CREATE TABLE table [(column, column...)] AS subquery;

Violating Constraints

Query :

• UPDATE emp SET department_id = 29 WHERE department_id = 30;

Error :

• insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk"

DETAIL :

• Key (deptno)=(29) is not present in table "dept"
• Department 29 does not exist

You cannot delete a row that contains a primary key that is used as a foreign key in another table.

Query :

• DELETE FROM departments WHERE department_id = 60;

Error :

• update or delete on table "dept" violates foreign key constraint "emp_re f_dept_fk" on table "emp";

Detail :

• Key (deptno)=(10) is still referenced from table "emp".
  

CHECK Constraint

CHECK Constraint :

Defines a condition that each row must satisfy

• The following expressions are not allowed :
• References to CURR_VAL, NEXT_VAL and CTID pseudocolumns
• Queries that refer to other values in other rows
• ..., salary numeric(2)CONSTRAINT emp_salary_minCHECK (salary > 0),...

CREATE TABLE : Example

FOREIGN KEY Constraint

• 
• Defined at either the table level or the column level
• CREATE TABLE emp(employee_id numeric(6), last_name varchar(25) NOT NULL, email varchar(25), salary numeric(8,2), commission_pct numeric(2,2), hire_date DATE NOT NULL, ... department_id numeric(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES dept(deptno), CONSTRAINT emp_email_uk UNIQUE(email));

FOREIGN KEY Constraint - Keywords :

• FOREIGN KEY : Defines the column in the child table at the table-constraint level
• REFERENCES : Identifies the table and column in the parent table
• ON DELETE CASCADE : Deletes the dependent rows in the child table when a row in the parent table is deleted
  
• ON DELETE SET NULL : Converts dependent foreign key values to null
  

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

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
  

DEFAULT Option in SQL

DEFAULT Option :

• Specify a default value for a column during an insert.
• ... hire_date DATE DEFAULT CURRENT_DATE, ...
• Literal values, expressions, or SQL functions are legal values.
• Another column’s name or a pseudo column are illegal values.
• The default data type must match the column data type.
• CREATE TABLE hire_dates (id numeric(8), hire_date DATE DEFAULT CURRENT_DATE);

Create the table :

• CREATE TABLE dept_info (deptno numeric(2), dname varchar(14), loc varchar(13), create_date DATE DEFAULT CURRENT_DATE);
  

Confirm table creation :

• \d dept_info
  

Referencing Another User’s Tables

• Tables belonging to other users are not in the user’s schema.

• You should use the schema name as a prefix to those tables.
• 
  

CREATE TABLE Statement

You must have:

• storage area

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

You specify:

• Table name
• Column name, column data type, and column size
• 
• 

Schema

Schema :

• A PostgreSQL database cluster contains one or more named databases.
• A database contains one or more named schemas, which in turn contain tables.
• The same object name can be used in different schemas without conflict;
• A user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.

Why Schemas :

• There are several reasons why one might want to use schemas :
  – To allow many users to use one database without interfering with each other.
  – To organize database objects into logical groups to make them more manageable.
  – Third-party applications can be put into separate schemas so they cannot collide with the names of other objects.

Creating a Schema :

• To create a schema, use the CREATE SCHEMA command.
• Give the schema a name preferably same as the user name so that it gets associated
  automatically.
• For example: CREATE SCHEMA training;
• Objects in this schema can be referred using schema.table for e.g training.students.
  

Using DDL Statements to Create and Manage Tables

Database Objects :


Naming Rules :

Table names and column names:
– Must begin with a letter
– Must be 1–30 characters long
– Must contain only A–Z, a–z, 0–9, _, and $
– Must not duplicate the name of another object owned by the same user
– Must not be an PostgreSQL server reserved word