Basic Data Definition Language(DDL) Commands

The Data Definition Language( DDL ) commands are used for creating new or modify existing schema of  a relation. You can also set constraints such as key constraints, foreign key constraints, etc on a relation.

Create Table Command

This command creates a new relation or table. You must specify the fields and data type for each field while using this command.

 

Figure 1: Create Table MANAGER
Figure 1: Create Table MANAGER

 

Alter Table Command

This is most important of DDL commands because it allow us to modify the table anytime.
Here are some examples for ALTER TABLE command

Adding a primary key for the table. A primary key uniquely identify a tuple in a relation.

 

Figure 2: Alter Table for MANAGER for Primary Key Constraint
Figure 2: Alter Table  MANAGER for Primary Key

 

Adding a foreign key for the table. A foreign key in a relation refer to primary key of another relation and cannot be null called the Referential Integrity.

 

Figure 3: Alter Table MANAGER for Foreign Key - Error
Figure 3: Alter Table MANAGER for Foreign Key – Error

In the above example, we receive an error because the attribute ‘DEPTNO’ is not set as primary key in relation ‘EX_DEPT’.

After adding ‘DEPTNO’ as primary key we are able to set the foreign key for ‘MANAGER’ relation.

Figure 4: Alter Table MANAGER for Foreign Key - Success
Figure 4: Alter Table MANAGER for Foreign Key – Success

 

Adding a new column in a Table. In the following example, we are adding ‘PCODE‘ in the ‘MANAGER’ table.

Figure 5: Alter Table MANAGER for new column
Figure 5: Alter Table MANAGER for new column

 

Removing a column from a relation or table. In the following example, we are removing the ‘PCODE’ column from the ‘MANAGER’ table.

 Figure 6: Removing a Column from the Table MANAGER
Figure 6: Removing a Column from the Table MANAGER

DESC <Table Name>

The ‘DESC’ command helps view the schema of the relation.

Figure 7: DESC <table name> display the table schema
Figure 7: DESC <table_name> display the table schema

DROP TABLE <table name>

This command will drop the table permanently.

Figure 8: DROP < table_name> will delete the Table
Figure 8: DROP < table_name> will delete the Table

Bibliography

Avi Silberschatz, Henry F. Korth, and S. Sudarshan. 27-Jan-2010. Database System Concepts. McGraw-Hill Education.

Ramakrishnan, Johannes Gehrke and Raghu. 1996. Database Management Systems. McGraw Hill Education; Third edition (1 July 2014).

Wikipedia. n.d. Data definition language. Accessed March 14, 2018. https://en.wikipedia.org/wiki/Data_definition_language.

Advertisements


 

Advertisements