SQL Create DB SQL Drop DB SQL Backup DB SQL Create Table SQL Drop Table SQL Alter Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key SQL Check SQL Default SQL Index SQL Auto Increment SQL Dates SQL Views SQL. The SQL CREATE TABLE Statement. The CREATE TABLE statement is used to create a new table in a database. AUTO INCREMENT Field. Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Summary: in this tutorial, you will learn how to use the SQL Server CREATE TABLE statement to create a new table.

Introduction to the SQL Server CREATE TABLE statement

Tables are used to store data in the database. Tables are uniquely named within a database and schema. Each table contains one or more columns. And each column has an associated data type that defines the kind of data it can store e.g., numbers, strings, or temporal data.

To create a new table, you use the CREATE TABLE statement as follows:

In this syntax:

• First, specify the name of the database in which the table is created. The database_name must be the name of an existing database. If you don’t specify it, the database_name defaults to the current database.
• Second, specify the schema to which the new table belongs.
• Third, specify the name of the new table.
• Fourth, each table should have a primary key which consists of one or more columns. Typically, you list the primary key columns first and then other columns. If the primary key contains only one column, you can use the PRIMARY KEY keywords after the column name. If the primary key consists of two or more columns, you need to specify the PRIMARY KEY constraint as a table constraint. Each column has an associated data type specified after its name in the statement. A column may have one or more column constraints such as NOT NULL and UNIQUE.
• Fifth, a table may have some constraints specified in the table constraints section such as FOREIGN KEY, PRIMARY KEY, UNIQUE and CHECK.

Note that CREATE TABLE is complex and has more options than the syntax above. We will gradually introduce you to each individual options in the subsequent tutorials.

SQL Server CREATE TABLE example

The following statement creates a new table named sales.visits to track the customer in-store visits:

In this example:

Because we do not specify the name of the database explicitly in which the table is created, the visits table is created in the BikeStores database. For the schema, we specify it explicitly, therefore, the visits table is created in the sales schema.

The visits table contains six columns:

• The visit_id column is the primary key column of the table. The IDENTITY(1,1) instructs SQL Server to automatically generate integer numbers for the column starting from one and increasing by one for each new row.
• The first_name and last_name columns are character string columns with VARCHAR type. These columns can store up to 50 characters.
• The visited_at is a DATETIME column that records the date and time at which the customer visits the store.
• The phone column is a varying character string column which accepts NULL.
• The store_id column stores the identification numbers which identify the store where the customer visited.
• At the end of the table’s definition is a FOREIGN KEY constraint. This foreign key ensures that the values in the store_id column of the visits table must be available in the store_id column in the stores table. You will learn more about the FOREIGN KEY constraint in the next tutorial.

In this tutorial, you have learned how to use the SQL Server CREATE TABLE statement to create a new table in a database.

This article demonstrates how to create a primary key in SQL Server when creating a table using Transact-SQL.

A primary key is one or more columns that have been configured as the unique identifier for a given table. Primary keys can be used to enforce data integrity in the table.

A table can only have one primary key, and primary keys can only be added to columns that are defined as NOT NULL.

This article demonstrates how to create a primary key in a new table (i.e. when creating the table). If you need to create a primary key in an existing table, see How to Add a Primary Key to an Existing Table in SQL Server.

Example 1 – Create a Table with a Primary Key

First I’ll create a test database:

Now create a new table that includes a primary key constraint:

This created a new table called Colors which has a primary key constraint on its ColorId column.

Example 2 – Check the Primary Key Constraint

We can run the following code to return a list of primary key constraints in the database:

Result:

I’ve narrowed the columns down for this example. The sys.key_constraints system view returns more columns than this. You can always use the * wildcard to return all columns if you wish.

We can see by the query result that this database has only one primary key (the one we just created).

Generate Primary Key When Creating A Table Sql File

In this case the primary key was automatically named by the system. You also have the option of providing your own name (more on that later).

Example 3 – Check the Index

By default a clustered index is created when you create the primary key. You can specify the clustered index explicitly or let it be created automatically. You also have the option of specifying a nonclustered index.

Here’s a query that returns the index that was created automatically when I created the above primary key:

Result (using vertical output):

In this case I narrowed the results to just the row that contains the same name of the primary key I just created. You can always remove the WHERE clause if you need more results to be returned.

We can see that this index has a type_desc of CLUSTERED.

Example 4 – Naming the Primary Key

The primary key that we created above was automatically named by the system. You can provide your own name if you prefer.

Here’s an example of specifying a name for the primary key. In this case I also specify a nonclustered index.

In this case I use the optional CONSTRAINT keyword to indicate the start of the definition of the primary key, followed by my chosen name for the primary key. Generate secure encryption key php. I also use the NONCLUSTERED keyword to specify that an unclustered index.

Check the primary key:

Result:

Check the index:

Result (using vertical output):

So we can see that this time the type_desc is NONCLUSTERED.

Note that when creating a table, CLUSTERED can be specified for only one constraint. If it’s specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY defaults to NONCLUSTERED.

Example 5 – Create a Primary Key on a Nullable Column

A primary key can only be created for columns that are defined as NOT NULL. If you try to create a primary key on a column that is set to NULL, you’ll get an error.

However, if you don’t specify the nullability, the column is set to NOT NULL by default.

To demonstrate this, let’s create another table, but this time, we’ll set it to NULL:

Result:

As expected, we get an error.

Let’s remove NULL from the table definition and try again:

Result:

This time the table was created successfully.

Let’s take a look at it:

Result:

Generate Primary Key When Creating A Table Sql Tutorial

So we can see that it’s not nullable, because the is_nullable flag is set to 0.

Example 6 – Primary Key on Multiple Columns

You can also create a primary key on multiple columns. Multicolumn primary keys are also known as composite primary keys. To create a composite primary key, simply separate the columns with a comma when defining the key.

Like this:

Sql Server Create Primary Key

Here’s an example of a situation where a multicolumn primary key could be used:

In this example, the BandMember table has a multicolumn primary key. In this case each column in the primary key is also a foreign key to the primary key of another table, but this is not a requirement.

See How to Create a Composite Primary Key in SQL Server for a more detailed explanation of this example.

Also see How to Create a Composite Foreign Key in SQL Server for an example that takes it a step further with a multicolumn foreign key that references the above composite primary key.