SQL – Create Table

Create Table is a DDL (Data definition language) statement. The CREATE TABLE is used to create and define a new relational table.

CREATE TABLE Statement :

• Creates a table with one or more columns of the specified dataType.
• With NOT NULL, system rejects any attempt to insert a null in the column.
• Can specify a DEFAULT value for the column.
• Primary keys should always be specified as NOT NULL.
• FOREIGN KEY clause specifies FK along with the referential action.
• To create a table the user must have Create table privileges and a storage area in which to create the table

Syntax Of CREATE TABLE Statement :

Although syntax for creating table in different databases is more or less same but it is advisable to do a little search on internet for your particular database.

Syntax –

CREATE TABLE [SCHEMA.]Tablename

(column_name data type [DEFAULT expr],

column_name data type [default expr], …)

Example :
CREATE TABLE product
(product_id NUMBER(5),
product_name VARCHAR2(10))
In Syntax :

• SCHEMA is same as the owner’s name.
• Table name is the name of the table.
• DEFAULT expr specifies a default value
• Column_name is the name of the column.
• Data type is the column’s data type and its length.

Create table with constraints:

Constraint is a rule that restricts the values for a column on which it is defined. Constraints check data as it is entered or updated in the database and prevent data that does not conform to the constraint’s rule from being entered.
Constraints are classified into two types:

1. Column level constraints
2. Table level constraints

NOT NULL : NOT NULL specifies that a column must have some value.
NULL : NULL (default) allows NULL values in the column.
DEFAULT : Specifies some default value if no value entered during INSERT
UNIQUE : Specifies that column(s) must have unique values
PRIMARY KEY : Specifies that column(s) must have unique values. Index is automatically generated for column
FOREIGN KEY : Specifies that column(s) are defined primary key in another table. Used for referential uniqueness of parent table. Index is automatically generated for column.

Rules for naming a table:

There are certain rule to name a new table in database (I’m writing here for Oracle, for other databases please confirm):

• The name must begin with a letter, A-Z or a-z.
• May contain letters, numeric and special character _(underscore). The $ and # are also legal but discouraged;
• The name is the same whether upper or lower case letters are used
• It may be up to 30 char length
• The name must not duplicate the name of any other object in your account.