A database table is used to store records (data). To create a database table, we use the SQL CREATE TABLE
statement. For example,
CREATE TABLE Companies (
id int,
name varchar(50),
address text,
email varchar(50),
phone varchar(10)
);
Here, the SQL command creates a database named companies. The table contains column (field) id, name, address, email and phone.
The int
, varchar(50)
and text
are data types that tell what data could be stored in that field. Some commonly used data types are as follows.
Data Type | Description | Example |
---|---|---|
int |
can store numbers | 400 , -300 |
varchar(x) |
can store variable characters with maximum length of x | John Doe , United States of America |
text |
can store texts up to 65535 characters | This is a really long paragraph that can go over lines. |
Note: We must provide data types for each column while creating a table. Learn more about SQL Data Types.
CREATE TABLE IF NOT EXISTS
While creating a table that already exists, throws an error. To fix this issue, we can add the optional IF NOT EXISTS
command while creating a table. For example,
CREATE TABLE IF NOT EXISTS Companies (
id int,
name varchar(50),
address text,
email varchar(50),
phone varchar(10)
);
Here, the SQL command will only create a table if there is not one with a similar name.
CREATE TABLE AS
We can also create a table using records from any other existing table using the CREATE TABLE AS
command. For example,
CREATE TABLE USACustomers
AS (
SELECT *
FROM Customers
WHERE country = 'USA'
);
Here, the SQL command creates a table named USACustomers and copies the records of the nested query into the new table.
Related Topics: CREATE TABLE
To create a primary key, we can write the following command.
In MySQL
CREATE TABLE Companies (
id int,
name varchar(50),
address text,
email varchar(50),
phone varchar(10),
PRIMARY KEY (id)
);
In Oracle and SQL Server
CREATE TABLE Companies (
id int NOT NULL PRIMARY KEY,
name varchar(50),
address text,
email varchar(50),
phone varchar(10)
);
To learn more, visit SQL PRIMARY KEY.
We can also add different types of constraints while creating a table. For example,
CREATE TABLE Companies (
id int NOT NULL,
name varchar(50) NOT NULL,
address text,
email varchar(50) NOT NULL,
phone varchar(10)
);
Here, the constraint NOT NULL
is added to the columns id, name and email. It simply means, these columns can't be empty (NULL).
Note: Sometimes these constraints are database specific, meaning that these keywords may vary database to database. We'll cover the major databases in our tutorials.
Recommended Readings