Data Definition Language Statements
In this chapter you will gain a deeper understanding of the three Data Definition Language statements – CREATE, ALTER and DROP. Using SQLiteStudio, you will also learn how to encode the corresponding SQL statements that handle the database structure.
Again, a database object is any defined logical unit that stores or references data. When you have a collection of database objects, you create a schemathat is associated with one particular database owner. The focus of this chapter is the basic form of data storage, which is the relational database table. A simple table further consists of rows, which corresponds to the records of data, and columns, which are also known as fields that contain an assigned particular type of data. A database table will always have at least one column and a row that is composed of one or more fields.
CREATE Statement
The numerous forms of the SQL CREATE statement are responsible for constructing vital database structures and objects – tables, views, schemas, domains and so on. The act of creating tables could be easy, but you need to take into consideration numerous factors. Planning table structures before actual implementation could save you time and effort since you do not need to reconfigure after the tables have been created.
Here are some of the factors to take into consideration through when creating tables: • Type of data the table will contain • Table and column names • Primary key (the column that makes each row of data unique to avoid duplicate records in a table) • Column length • Columns containing null values
Syntax: CREATE TABLETABLE_NAME (field1 DATA_TYPE [not null], field2 DATA_TYPE [not null], field3 DATA_TYPE [not null], field4 DATA_TYPE [not null],
ALTER Statement
The SQL ALTER statement is used to modify database objects, specifically tables. Altering table elements can include adding and dropping columns, changing column definitions, adding and dropping constraints, modifying table storage values and more.
Syntax: ALTER TABLETABLE_NAME[modify] [columnCOLUMN_NAME] [DATA TYPE| null not null] [restrict | cascade] [drop] [constraintCONSTRAINT_NAME] [add] [column]COLUMN DEFINITION;
• Alter a Table by Adding a New Column
1. A new column that contains the company address of the customer will be added to the Customer_TBLtable. Type the following lines of code under the QUERY tab:
ALTER TABLE Customer_TBL ADD CompanyAdd VARCHAR;
