Structured Query Language - CREATE TABLE Statement Explained
Conventions used in writing the syntax:
1. Pair of square brackets ([]) represent optional portion. They
may be used if necessary. They are not compulsory components in creating a
table.
2. All the words given in Bold
letters are Reserved Words (system defined key words).
3. The pipe symbol (|) is used to represent OR.
4. Pair of Curly Brackets ({}) used to represent groups. For example, Column-i-Definition mean the whole
thing {Column-i-Name Data-type (size) [Column-Level-Constraint][DEFAULT value]}.
CREATE TABLE Table_Name
(Column-1-Definition,
Column-2-Definition,
Column-3-Definition,
…,
Column-N-Definition,
[Table-Level-Constraint-1,
Table-Level-Constraint-2,
…,
Table-Level-Constraint-N])
Column-i-Definition:
{ Column-i-Name
Data-type (size) [Column-Level-Constraint][DEFAULT
value] }
Column-Level-Constraint:
{[NOT NULL] | [CONSTRAINT constraint-name] {[CHECK
(search-condition) | PRIMARY KEY | UNIQUE | REFERENCES clause]}}
Table-Level-Constraint:
{ [CONSTRAINT constraint-name]
[CHECK (search-condition)] | [PRIMARY KEY (column-name1 [,
column-name2, …])] | [UNIQUE (column-name1
[, column-name2, …])] | FOREIGN KEY (column-name1
[, column-name2, …]) REFERENCES
clause] }
REFERENCES clause:
{ REFERENCES table-name [(column-name1
[, column-name2, …])] }
Discussions:
Column-i-Definition:
Any column defined as part of the table is of the form “column-name
data-type(size)”. For example, the column Emp_Name can be defined as “Emp_Name
VARCHAR(25)”. The Column-Level-Constraint or DEFAULT is optional.
DEFAULT – represents that the value for this column would be passed
while defining the table structure. While entering a record into the table, if
you leave the DEFAULT column without any values, the default value gets stored.
Can we create a complete table
at this level using Column Definitions?
Yes. We can design simple table without much Integrity Constraints.
Example 1:
CREATE TABLE Employee ( Emp_No CHAR (5),
Emp_Name VARCHAR(25),
Dept_No Number(3),
Branch_Location VARCHAR(25),
DOB DATE,
Phone Number(10));
Example 2:
CREATE TABLE Employee ( Emp_No CHAR (5),
Emp_Name VARCHAR(25),
Dept_No Number(3) DEFAULT 1,
DOB DATE,
Branch_Location VARCHAR(25) DEFAULT ‘CHENNAI’,
Phone Number(10));
First example creates a
table named Employee with 6 columns with the type and size specified.
Second example creates a
table named Employee with 6 columns with the type and size specified. Along
with that, it also defines default value 1 for Dept_No attribute, and default value ‘CHENNAI’ for Branch_Location attribute. If for any
records, the value of Dept_No and Branch_Location attributes are not
provided, the system according to the definition inserts 1 and ‘CHENNAI’ into
the table automatically in the respective columns.
Column-Level-Constraint:
Column-level-constraint is a constraint (condition) which refers to a
single column in which it is defined. Except CHECK constraints, all the other
constraints need not specify a column in its definition. The following are the
Column-level constraints in SQL;
NOT NULL –
represents that the column must have a value. We cannot leave it blank or we
cannot use NULL value.
PRIMARY KEY –
represent the key for the whole relation (table) to uniquely identify a record
among other records of the same table. PRIMARY KEY means both UNIQUE and NOT NULL.
UNIQUE – represents
that the value for this column must be unique, and should not be repeated in
any other records of the same table.
FOREIGN KEY –
represents that the value accepted for the column must be available already in
the column from which it is referred.
CHECK – represents that the search condition must be satisfied while
entering a value into this column.
Can we create a complete table
at this level using Column Definitions?
Yes. At this level we could use some integrity constraints at the
column level.
Example 3:
CREATE TABLE Employee ( Emp_No CHAR (5) PRIMARY KEY,
Emp_Name VARCHAR(25) NOT NULL,
Dept_No Number(3) CHECK (Dept_No<>0),
DOB DATE,
Branch_Location VARCHAR(25) DEFAULT ‘CHENNAI’,
Phone Number(10) UNIQUE);
Example 4:
CREATE TABLE Employee ( Emp_No CHAR (5) PRIMARY KEY,
Emp_Name VARCHAR(25) NOT NULL,
Dept_No Number(3) CHECK (Dept_No>0) REFERENCES Dept(Deptno),
DOB DATE,
Branch_Location VARCHAR(25) DEFAULT ‘CHENNAI’,
Phone Number(10) UNIQUE);
CREATE TABLE Dept(Deptno Number(3) PRIMARY KEY,
Dept_Name VARCHAR(20),
Address VARCHAR(20));
Example 3 creates table Employee with all the column level constraints. As you see in the example, the
constraints are defined at column level for every single column if necessary. The
column Dept_No uses one CHECK constraint. Through this CHECK constraint, the
design of the table enforces that the value for this column must be greater
than 0. The REFERENCES clause of column Dept_No
checks the value you insert into the column against the values stored in the Deptno column of table Dept which is referred by Employee. Here,
it is mandatory to have the columns the foreign key and referred attributes
with same data types and sizes.
Some of the CHECK constraints;
Emp_no CHAR(5) CHECK (Emp_No LIKE ‘E___’) – this says every value of
Emp_no must start with the letter ‘E’ and have exactly 4 characters.
Branch_Location VARCHAR(25) CHECK (Branch_Location IN (‘Chennai’, ‘Mumbai’,
‘New Delhi’)) – this means, the table can permit one of three values given in
the CHECK condition for Branch_Location attribute.
Table Level Constraint:
Table level constraint does everything as we did with column level
constraint with groups of many attributes. That is the integrity constraints
applied collectively on set of attributes. They refer to one or more columns in
the table. They specify the names of the attributes to which they apply explicitly.
Table-level CHECK constraints can refer to 0 or more columns in the table.
Example:
CREATE TABLE Employee ( Emp_No CHAR (5),
Emp_Name VARCHAR(25) NOT NULL,
Dept_No Number(3),
DOB DATE,
Branch_Location VARCHAR(25) DEFAULT ‘CHENNAI’,
Phone Number(10) UNIQUE,
CONSTRAINT pk PRIMARY KEY (Emp_No),
CONSTRAINT ck1 CHECK (Dept_No>0),
CONSTRAINT fk1 FOREIGN KEY (Dept_No) REFERENCES Dept(Deptno));
CREATE TABLE Dept(Deptno Number(3) PRIMARY KEY,
Dept_Name VARCHAR(20),
Address VARCHAR(20));
In example 5, everything we have done in Example 4 is repeated with a
slight modification. In example 5, we have done it in table level. Hence, the
column names of all the constraints are repeated inside pair of brackets. And
the new thing here is that the keyword CONSTRAINT which is primarily used to
name different constraints which in future might be used for Altering the table
by modifying or deleting the existing constraints. We need not give a
constraint name for PRIMARY KEY, but for all the others use of constraint name
is recommended. The very reason is a table can have more CHECK constraints and
more FOREIGN KEY constraints.
Basic Data Types:
CHAR(size) - To store
alpha-numeric character values. The size represents maximum number of
characters. This type stores static values. That is, it occupies the memory as
per the size even though the value is of smaller size.
Example – Emp_No CHAR(5) –
can store employee numbers of maximum 5 characters. Even, if you store smaller
value, say for example, 3 characters, the system reserves and occupies 5
characters in memory (2 gets wasted).
VARCHAR(size) – To store
alpha-numeric character values. This type is dynamic, i.e, occupies the memory
according to these size.
Example – Emp_Name
VARCHAR(25) – can store employee names of maximum length 25. Smaller names will
occupy lesser memory.
DATE – To store the date values.
Example – DOB DATE – stores the date of
birth in date format (default format – ‘dd-mon-yyyy’)
NUMBER(digits) – To store numbers of
size digits.
Example – Phone NUMBER(10) – stores
phone number of size 10
NUMBER(digits, decimal point digits) –
To store numbers with decimal point values.
Example – Salary NUMBER(6,2) –stores
the salary value in the format 1000.50. Here, 6 mean the total length and 2
means the decimal point digits out of 6 digits.
************************
Related links:
