Creating a table

 

A table can be visualized as a two-dimensional arrangement of data that consists of rows and columns. To create a table, use the CREATE TABLE statement.

The row is the horizontal part containing one or more columns. The column is the vertical part containing one or more rows of data of one data type. All data for a column must be of the same type. A table in SQL is a keyed or non-keyed physical file.

You can create a table using the CREATE TABLE statement. The definition must include its name and the names and attributes of its columns. The definition can include other attributes of the table, such as the primary key.

Example: Given that you have administrative authority, create a table named 'INVENTORY' with the following columns:

• Part number: Integer between 1 and 9999, and must not be null

• Description: Character of length 0 to 24

• Quantity on hand: Integer between 0 and 100000

The primary key is PARTNO.

CREATE TABLE INVENTORY                  (PARTNO         SMALLINT     NOT NULL,
                  DESCR          VARCHAR(24 ),
                  QONHAND        INT,
                  PRIMARY KEY(PARTNO))

• Adding and removing constraints
  Constraints can be added to a new table or to an existing table. To add a unique or primary key, a referential constraint, or a check constraint, use the CREATE TABLE or the ALTER TABLE statement. To remove a constraint, use the ALTER TABLE statement.

• Referential integrity and tables
  Referential integrity is the condition of a set of tables in a database in which all references from one table to another are valid.

• Example: Removing constraints
  When you remove the primary key over the DEPTNO column in the DEPARTMENT table, other tables are affected.

• Check pending
  Referential constraints and check constraints can be in a check pending state, where potential violations of the constraints exist.

 

Parent topic:

Data definition language

 

Related concepts

Data types