Create a database table, add constraints (primary key, unique, check, Not
Ex.No : 1a
null) using SQL DDL
AIM
To create a database table, add constraints (primary key, unique, check, Not null using SQL DDL
commands.
DDL COMMANDS:
Data Definition Language (DDL) or Schema Definition Language, statements are used to define
the database structure or schema.
● CREATE - to create objects in the database
● ALTER - alters the structure of the database
● DROP - delete objects from the database
● TRUNCATE - remove all records from a table, including all spaces allocated for the records are
removed
● RENAME - rename an object
CREATE Command:
This command is used to create a table.
Syntax:
SQL> CREATE TABLE TABLENAME (COLUMN NAME1 <DATATYPE> (SIZE),
COLUMN NAME.2 <DATATYPE> (SIZE) );
Example:
SQL>CREATE TABLE EMP (EMPNO NUMBER (4),ENAME VARCHAR2 (10),
DESIGNATION VARCHAR2 (10),SALARY NUMBER (8, 2));
Table created
TABLE DESCRIPTION
It is used to view the table structure to confirm whether the table was created correctly.
Syntax:
SQL> DESC <TABLE NAME>;
Example:
SQL> DESC EMP;
ALTER Command:
This command is used to add, delete, and modify columns in an existing table.
721222104048
ALTER_ADD Command
To add a new column in a table
Syntax:
SQL> ALTER TABLE <TABLE NAME> ADD (<COLUMN NAME><DATATYPE><SIZE>);
Example:
SQL> ALTER TABLE EMP ADD QUALIFICATION VARCHAR2(6);
Table altered.
SQL> DESC EMP;
ALTER_Remove /Drop Command
It will delete the table structure provided the table should be empty.
Syntax:
SQL> ALTER TABLE <TABLE NAME> DROP COLUMN <COLUMN NAME>;
Example:
SQL> ALTER TABLE EMP DROP COLUMN DOJ;
Table altered.
SQL> DESC EMP;
ALTER_Modify Command
To change the data type of a column in a table
Syntax:
SQL > ALTER TABLE <TABLE NAME> MODIFY <COLUMN NAME><DATATYPE> (SIZE);
Example:
SQL>ALTER TABLE EMP MODIFY EMPNO NUMBER (6);
Table altered.
SQL> DESC EMP;
RENAME Command
This command is used to rename a table.
Syntax:
SQL> ALTER TABLE RENAME <OLD NAME> TO <NEW NAME>
Example:
SQL> ALTER TABLE RENAME EMP TO EMPLOYEE;
SQL> DESC EMP;
721222104048
TRUNCATE Command
If there is no further use of records stored in a table and the structure has to be retained then the
records alone can be deleted.
Syntax:
TRUNCATE TABLE <TABLE NAME>;
Example:
Truncate table EMP;
DROP Command
To remove a table along with its structure and data.
Syntax:
SQL> Drop table<table name>;
Example:
SQL> drop table employee;
Constraints in SQL
Constraints in SQL means we are applying certain conditions or restrictions on the database.
Constraints in SQL can be categorized into two types:
1. Column Level Constraint:
Column Level Constraint is used to apply a constraint on a single column.
2. Table Level Constraint:
Table Level Constraint is used to apply a constraint on multiple columns.
Constraints available in SQL are:
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. CHECK
1. NOT NULL
● NULL means empty, i.e., the value is not available.
● Whenever a table's column is declared as NOT NULL, then the value for that column cannot
be empty for any of the table's records.
● There must exist a value in the column to which the NOT NULL constraint is applied.
NOTE: NULL does not mean zero. NULL means empty column, not even zero.
Syntax to apply the NOT NULL constraint
CREATE TABLE TableName (ColumnName1 datatype NOT NULL, ColumnName2 datatype,.,
ColumnNameN datatype);
721222104048
Example:
Create a student table and apply a NOT NULL constraint on one of the table's column while creating a
table.
CREATE TABLE student(StudentID INT NOT NULL, Student_FirstName VARCHAR(20), Student_La
stName VARCHAR(20), Student_PhoneNumber VARCHAR(20), Student_EmailID VARCHAR(20));
mysql> DESC student;
Syntax to apply the NOT NULL constraint on an existing table's column:
ALTER TABLE TableName CHANGE Old_ColumnName New_ColumnName Datatype NOT NULL;
Example:
Consider we have an existing table student, without any constraints applied to it. Later, we decided to
apply a NOT NULL constraint to one of the table's column. Then we will execute the following query:
mysql> ALTER TABLE student CHANGE StudentID StudentID INT NOT NULL;
To verify that the not null constraint is applied to the student table's column, we will execute the
following query:
mysql> DESC student;
2. UNIQUE
● Duplicate values are not allowed in the columns to which the UNIQUE constraint is applied.
● The column with the unique constraint will always contain a unique value.
● This constraint can be applied to one or more than one column of a table, which means more than
one unique constraint can exist on a single table.
● Using the UNIQUE constraint, you can also modify the already created tables.
Syntax to apply the UNIQUE constraint on a single column:
CREATE TABLE TableName (ColumnName1 datatype UNIQUE, ColumnName2 datatype,.,
ColumnNameN datatype);
721222104048
Example:
Create a student table and apply a UNIQUE constraint on one of the table's column while creating a table.
mysql> CREATE TABLE student(StudentID INT UNIQUE,Student_FirstName VARCHAR(20), Student
_LastName VARCHAR(20), Student_PhoneNumber VARCHAR(20),Student_EmailID VARCHAR(20));
To verify that the unique constraint is applied to the table's column and the student table is created
successfully, we will execute the following query:
mysql> DESC student;
Syntax to apply the UNIQUE constraint on more than one column:
CREATE TABLE TableName (ColumnName1 datatype, ColumnName2 datatype,., ColumnNameN dat
atype, UNIQUE (ColumnName1, ColumnName 2));
Example:
Create a student table and apply a UNIQUE constraint on more than one table's column while creating a
table.
mysql> CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Student_LastN
ame VARCHAR(20), Student_PhoneNumber VARCHAR(20), Student_Email_ID VARCHAR(40), U
NIQUE(StudentID, Student_PhoneNumber));
mysql> DESC student;
3. PRIMARY KEY
● PRIMARY KEY Constraint is a combination of NOT NULL and Unique constraints.
● NOT NULL constraint and a UNIQUE constraint together forms a PRIMARY constraint.
● The column to which we have applied the primary constraint will always contain a unique value and
will not allow null values.
Syntax of primary key constraint during table creation:
CREATE TABLE TableName (ColumnName1 datatype PRIMARY KEY, ColumnName2 datatype,., ColumnNa
meN datatype);
721222104048
Example:
Create a student table and apply the PRIMARY KEY constraint while creating a table.
mysql> CREATE TABLE student(StudentID INT PRIMARY KEY, Student_FirstName VARCHAR(20), Stude
nt_LastName VARCHAR(20), Student_PhoneNumber VARCHAR(20), Student_Email_ID VARCHAR(40));
To verify that the primary key constraint is applied to the table's column and the student table is created
successfully, we will execute the following query:
mysql> DESC student;
4. CHECK
● Whenever a check constraint is applied to the table's column, and the user wants to insert the value in it, then
the value will first be checked for certain conditions before inserting the value into that column.
Syntax to apply check constraint on a single column:
CREATE TABLE TableName (ColumnName1 datatype CHECK (ColumnName1 Condition), ColumnName2 dat
atype,., ColumnNameN datatype);
Example:
Create a student table and apply CHECK constraint to check for the age less than or equal to 15 while creating a
table.
mysql> CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Student_LastName VA
RCHAR(20), Student_PhoneNumber VARCHAR(20), Student_Email_ID VARCHAR(40), Age INT CHECK(
Age <= 15));
To verify that the check constraint is applied to the student table's column, we will execute the following query:
mysql> DESC student;
Syntax to apply check constraint on multiple columns:
CREATE TABLE TableName (ColumnName1 datatype, ColumnName2 datatype CHECK (ColumnName1 Cond
ition AND ColumnName2 Condition),., ColumnNameN datatype);
Example:
Create a student table and apply CHECK constraint to check for the age less than or equal to 15 and a percentage
greater than 85 while creating a table.
721222104048
mysql> CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Student_LastName VA
RCHAR(20), Student_PhoneNumber VARCHAR(20), Student_Email_ID VARCHAR(40), Age INT, Percentage
INT, CHECK( Age <= 15 AND Percentage > 85));
To verify that the check constraint is applied to the age and percentage column, we will execute the following
query:
mysql> DESC student;
OUTPUT:
CREATE COMMAND:
TABLE DESCRIPTION:
ALTER COMMAND:
ALTER_ADD COMMAND:
721222104048
ALTER_REMOVE/DROP COMMAND:
ALTER_MODIFY COMMAND:
RENAME COMMAND:
TRUNCATE COMMAND:
721222104048
DROP COMMAND:
RESULT:
Thus we created a database table and add constraints(primary key, unique,
check, Not null using SQL DL commands.
721222104048
