Did you find this useful?
Socialize it today.


SQL CREATE TABLE Statement

Monday, January 02, 2012

tags SQL

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



employeeIDemployeeNamehireDate
   

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 NULLRequires the field to contain data. This constraint will not allow NULL values.
PRIMARY KEYUniquely identifies each record in a database table
FOREIGN KEYA 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.
IDENTITYWhile 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.
DEFAULTThe 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'
)

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

Head First SQL: Your Brain on SQL A Learners Guide SQL Cookbook