SQL Tutorial

Creating and Modifying Tables

Data Types

All major relational database management systems allow DBAs to specify the type of data a field can contain. The table below shows the most commonly used data types specified in ANSI SQL.

Unfortunately, different RDBMSs use different terms for these data types. The terms for SQL Server, Oracle and MySQL are shown below. You'll notice that there is some variation.

Creating Tables

Now that you understand data types, you're ready to learn how to create a table. Note that you must have the proper database permissions to do so.

CREATE TABLE table_name
(
 column_name data_type,
 column_name data_type,
 column_name data_type,
 more columns...
)

NULL Values

As we have discussed, in some cases a value may not be set for a specific field in a specific record. In this case, that field contains NULL. In the CREATE TABLE statement, the default for each column is to allow for null values. This can be stated explicitly with the NULL flag or it can be changed with the NOT NULL flag.

CREATE TABLE table_name
(
 column_name data_type NOT NULL,
 column_name data_type NULL,
 column_name data_type NOT NULL,
 more columns...
)

Primary Keys

Every table should have a primary key, which is a field or group of fields that can be used to uniquely identify specific records in the table. Primary keys can be specified in two ways as shown below.

CREATE TABLE table_name
(
 column_name data_type PRIMARY KEY,
 column_name data_type null,
 column_name data_type not null,
 more columns...
)

CREATE TABLE table_name
(
 column_name_pk data_type,
 column_name data_type null,
 column_name data_type not null,
 more columns...,
 CONSTRAINT PK_table_name PRIMARY KEY (column_name_pk)
)

When a table uses a group of fields for the primary key, the key is called a composite primary key. In this case, you must specify the primary key at the end of the CREATE TABLE statement as shown below.

CREATE TABLE table_name
(
 column_name_pk1 data_type,
 column_name_pk2 data_type,
 column_name data_type not null,
 more columns...,
 CONSTRAINT PK_table_name PRIMARY KEY (column_name_pk1,column_name_pk2)
)

Foreign Keys

Foreign keys reference the primary key of another table, thereby creating a relationship between those two tables. They can be specified using the two methods shown below.

CREATE TABLE table_name
(
 column_name data_type PRIMARY KEY,
 column_name data_type null,
 column_name_fk data_type FOREIGN KEY REFERENCES table2_name(column_name_pk),
 more columns...
)

CREATE TABLE table_name
(
 column_name data_type PRIMARY KEY,
 column_name data_type null,
 column_name_fk data_type,
 more columns...,
 CONSTRAINT FK_table_name FOREIGN KEY REFERENCES table2_name(column_name_pk)
)

The code samples below show how to create duplicates of the Employees, Customers and Orders tables.

CREATE TABLE Employees2
(
 EmployeeID int NOT NULL PRIMARY KEY,
 LastName nvarchar(20) NOT NULL,
 FirstName nvarchar(10) NOT NULL,
 Title nvarchar(30) NULL,
 TitleOfCourtesy nvarchar(25) NULL,
 BirthDate datetime NULL,
 HireDate datetime NULL,
 Address nvarchar(60) NULL,
 City nvarchar(15) NULL,
 Region nvarchar(15) NULL,
 PostalCode nvarchar(10) NULL,
 Country nvarchar(15) NULL,
 HomePhone nvarchar(24) NULL,
 Extension nvarchar(4) NULL,
 ReportsTo int FOREIGN KEY REFERENCES Employees(EmployeeID)
)

CREATE TABLE Customers2
(
 CustomerID nchar(5) PRIMARY KEY,
 CompanyName nvarchar(40) NOT NULL,
 ContactName nvarchar(30) NULL,
 ContactTitle nvarchar(30) NULL,
 Address nvarchar(60) NULL,
 City nvarchar(15) NULL,
 Region nvarchar(15) NULL,
 PostalCode nvarchar(10) NULL,
 Country nvarchar(15) NULL,
 Phone nvarchar(24) NULL,
 Fax nvarchar(24) NULL
)

CREATE TABLE Orders2
(
 OrderID int PRIMARY KEY,
 CustomerID nchar(5) FOREIGN KEY REFERENCES Customers2(CustomerID),
 EmployeeID int FOREIGN KEY REFERENCES Employees2(EmployeeID),
 OrderDate datetime NULL,
 RequiredDate datetime NULL,
 ShippedDate datetime NULL,
 ShipVia int NULL,
 Freight decimal(8,2) NULL
)

Notice that all three tables have primary keys and that the Orders2 table has two foreign keys referencing the other two tables.

This demo uses SQL Server data types.

Adding and Dropping Columns

The syntax for adding a column to a table is shown below:

ALTER TABLE table_name
ADD column_name data_type

Note that you will either have to allow for NULL values or set a default value for the new column.

The syntax for dropping a column to a table is shown below:

ALTER TABLE table_name
DROP COLUMN column_name

Note that you cannot drop primary key columns.

Renaming Tables

The syntax for renaming tables is different in different RDBMSs.

SQL Server

SQL Server uses a built-in stored procedure for renaming tables:

sp_rename table_name, new_table_name

Oracle and MySQL

Oracle and MySQL use the RENAME command for renaming tables:

RENAME table_name TO new_table_name

Dropping Tables

Dropping tables is scarily easy; however, there are data integrity checks to make sure you don't drop tables that are referenced by other tables.

DROP TABLE table_name

All material in this Creating and Modifying Tables is copyright 2012 Webucator. The purpose of this website is to help you learn SQL on your own and use of the website implies your agreement to our SQL Tutorial Terms of Service.