The SQL CREATE TABLE statement can be used to create a table within a SQL database.
Syntax
CREATE TABLE tableName
(
columnName1 data_type,
columnName2 data_type,
columnName3 data_type,
columnName4 data_type,
...
)
Example
CREATE TABLE Employees
(
employeeID int,
employeeName varchar(255),
hireDate datetimestamp
)
Results
employeeID | employeeName | hireDate |
| | |
The CREATE TABLE Statement used in this example created a table with three columns. The first column is used to store
integer data. The second column stores string data (of type varchar) up to 255 characters and the third column is used to store
date/time information. After the table has been created, you can use other SQL statements, such as INSERT INTO to populate
data into this table. If you need to modify the table after it has been created, you could use the SQL ALTER statement.
When creating table, you will most likely need to use constraints to limit the
type of data that can go into a table. Constraints can be specified when a table
is created or after the table is created using the ALTER TABLE statement). Here is a list of the most common constraints:
NOT NULL | Requires the field to contain data. This constraint will not allow NULL values. |
PRIMARY KEY | Uniquely identifies each record in a database table |
FOREIGN KEY | A FOREIGN KEY in one table points to a PRIMARY KEY in another table. It is used to prevent invalid data from being inserted into the FOREIGN KEY field as well as actions that would destroy the relationship between the tables. |
IDENTITY | While not a constraint, IDENTITY is commonly used along with the PRIMARY KEY to auto increment the integer value on PRIMARY KEY for new records.
You must specify both the seed and increment or neither. If neither is specified, the default is (1,1). Example IDENTITY (1,1), OR IDENTITY. |
DEFAULT | The DEFAULT constraint is used to insert a default value into a column when no value is specified for the field during the inserting of new records. |
Example
CREATE TABLE Employees
(
employeeID int NOT NULL PRIMARY KEY IDENTITY,
employeeName varchar(255),
hireDate datetime,
deptID int FOREIGN KEY REFERENCES Department(Id),
status varchar(10) DEFAULT 'Unknown'
)
Did you find the page informational and useful? Share it using one of your favorite social sites.
Recommended Books & Training Resources