Various database servers have tools for creating a table by using GUI/Wizards.
You may provide the database table a name, create columns with data types, and may also set constraints like NOT NULL, Primary keys, auto-increment, etc. (depending on the database server like MS SQL Server, MySQL, Oracle, DB 2)
The GUI or wizards may vary, depending on the provider; however, one common way of creating tables in different relational DBMS is using the CREATE Table statement of SQL.
An example/syntax of SQL CREATE Table statement is:
CREATE TABLE tbl_example( ex_col1 INT NOT NULL, ex_col2 VARCHAR(100), ex_col3 CHAR(30), ex_col4 datetime, PRIMARY KEY (ex_col1) );
A few points based on this SQL code for creating a table:
- In the above sample example or syntax of CREATE TABLE, you can see the process of creating a table starts with CREATE TABLE
- This is followed by using the table name that may contain letters, numbers, or characters like underscores.
- Give the column names in that table and enclose them in parentheses.
- The column names should also follow certain naming conventions.
- With each column name, its data type is also provided along with constraint e.g. NOT NULL.
- At the last, you may use the PRIMARY KEY statement to define the primary key in that table. You may use one or more columns which are enclosed in parenthesis for a primary key.
The following section shows a few examples of creating tables. I will show you how to create tables step by step and add columns, constraints, primary keys, etc.
Creating employees table by CREATE TABLE statement
In this example, we used the CREATE TABLE command to create a table.
I am using MS SQL Server for demonstration – so any figures shown below are from the SQL Server database.
The query for creating the Sto_employee table:
CREATE TABLE Sto_employee( ID INT NOT NULL, emp_name VARCHAR(100), emp_salary decimal(8,2), emp_age tinyint, join_date datetime );
The above command should create a table, sto_employee. I used sto_ prefix for the table name as supposedly, this table is used to store the departmental store’s staff information.
A few columns with different data types are created, but no primary key is added. So, if you view that table in the design mode, this is how it looks:
Adding a primary key in a table example
The primary key is created at the table level to uniquely identify the rows.
The column with a primary key constraint cannot contain a NULL and duplicate value.
In our above CREATE TABLE example, though we created an ID with NOT NULL constraint, however, it may contain duplicate values; as this is not a primary key.
The SQL code below shows how to create the same table with ID as the primary key.
The design view is also shown so you may see the difference:
CREATE TABLE Sto_employee( ID INT NOT NULL, emp_name VARCHAR(100), emp_salary decimal(8,2), emp_age tinyint, join_date datetime PRIMARY KEY (ID) );
The table design view after executing this command:
You can see the yellow key sign for the ID field now. That represents a primary key in MS SQL Server.
For example:
Using Alter Table command for creating a primary key
Rather than first deleting the table and recreating it again; as I did for the above example to add the primary key, you may also use the ALTER TABLE command to add a primary key in the table.
Suppose, we have created the table as in the first example. Now, we will add a primary key for the created table as follows:
ALTER TABLE sto_employee ADD PRIMARY KEY (ID);
The ID column should be the primary key in the sto_employee table after executing the above command.
Creating a foreign key example
The table that contains the primary key (or candidate key) is called the parent table while the table that contains the foreign key is called the child table.
Before showing you the command of creating a foreign key, consider the table created in the above examples as the parent table.
Suppose, we need another table, sto_emp_salaries_paid that will be used for storing the salaries paid to employees every month.
As such, it may contain salaries of those employees only which data exists in the sto_employees table.
So, we can use the ID of sto_employees in the sto_emp_salaries_paid table as a foreign key (Remember, the ID is the primary key in sto_employees).
Now, have a look at the CREATE TABLE command for sto_emp_salaries_paid table with a foreign key in MS SQL Server, MS Access, and Oracle databases:
CREATE TABLE sto_emp_salaries_paid ( sal_id INT NOT NULL PRIMARY KEY, emp_id INT FOREIGN KEY REFERENCES sto_employee(ID), emp_sal_paid decimal(8,2), date_paid datetime, );
The table with a foreign key should be created by the above command. If you try entering an emp_id in the table that does not exist in the sto_employee table, an error should be generated.
Using SELECT INTO statement for creating/copying a table
You may copy complete structure/data or specific columns and rows to the new table by using column names and WHERE clause in the SELECT INTO statement.
The following query shows an example of creating a new table with three columns from our example table, sto_employee.
The Query:
SELECT ID, emp_name, emp_age INTO Sto_employee_2 FROM Sto_employee;
You may add the WHERE clause for only copying the specific rows in the new table from the existing table.
In Oracle database, you may use the CREATE TABLE…AS command for copying the existing table into the new table. An example query is:
CREATE TABLE Sto_employee_2 AS SELECT ID, emp_name, emp_salary FROM sto_employee;
An example query in the MySQL database for creating and copying the table data:
CREATE TABLE sto_employees_2 SELECT * FROM sto_employee;
This should create a new table and copy the data as well.