CREATE and ALTER Statement

Create Statement


The CREATE statement is used to create a new table with no record. Let's create the table office. The records in the office table will contain a technical id, the name of the office, a description, the number of available places, the availability and the date for the next office security control:

• Query:

CREATE TABLE office
(
   id_office INTEGER PRIMARY KEY NOT NULL,
   name VARCHAR(20) NOT NULL,
   description VARCHAR(255),
   place_number INTEGER NOT NULL,
   available SMALLINT NOT NULL DEFAULT 1,
   next_inspection DATE NOT NULL
);

• The table after the statement:

office
id_office	INTEGER
name	VARCHAR(20)
description	VARCHAR(255)
place_number	INTEGER
available	SMALLINT
next_inspection	DATE

 ALTER statement 

The ALTER statement is used to modify a table. It can be used on a table with records in it.

ADD CONSTRAINT clause

This clause allows to add a constraint on the table as it could be done at the table creation time. Let's add a unicity constraint on both the name and the description of the office:

• Query:

ALTER TABLE office ADD CONSTRAINT unique_name_and_description UNIQUE (name, description);

Now we can not insert a row with the same name and description of an already existing row and we can not update a row with the same name and description of another row. However, we can insert a row with only the same name or only the same description.

DROP CONSTRAINT clause

This clause allows to remove an existing constraint on the table by its name. Let's remove the preceding unicity constraint on both the name and the description of the office:

• Query:

ALTER TABLE office DROP CONSTRAINT unique_name_and_description;

Now we can insert a row with the same name and description of an already existing row and we can update a row with the same name and description of another row once again.
 

ADD COLUMN clause

• Query:

ALTER TABLE office ADD (ColumnName) SMALLINT DEFAULT 0;

DROP COLUMN clause

• Query:

ALTER TABLE office DROP (columnName);

DROP statement

The exhaustive syntax of the DROP statement for the tables is as follows:
DROP TABLE <table name>;
The DROP statement is used to remove table.