CREATE TABLE statement in SQL



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.

No comments:

Post a Comment