Blogs

SQL Server CREATE TABLE: Creating a New Table in the Database

Posted on by admin

Summary: In this walkthrough, 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 have a unique name within a database and schema. Each table contains one or more columns. And each column has an associated data type that defines the type of data it can store, for example, numbers, strings, or temporary data.

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

CREATE TABLE [database_name.] [schema_name.] table_name ( pk_column data_type PRIMARY KEY, column_1 data_type NOT NULL, column_2 data_type, …, table_constraints ); Code language: SQL (Structured Query Language) (sql)

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 do not specify it, the default database_name is the current database.
  • Second, specify the schema to which the new table belongs.
  • Third, specify the name of the new table.
  • Fourth, each table must have a primary key that 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 keywords PRIMARY KEY after the column name. If the primary key consists of two or more columns, you must 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 can have one or more column constraints, such as NOT NULL and UNIQUE.
  • Fifth, a table can have some constraints specified in the table restrictions section, such as FOREIGN KEY, PRIMARY KEY, UNIQUE key, and CHECK.

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

SQL Server

CREATE TABLE Sample

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

( visit_id INT PRIMARY KEY IDENTITY (1, 1), first_name VARCHAR (50) NOT NULL, last_name VARCHAR (50) NOT NULL, visited_at DATETIME, phone VARCHAR(20), store_id INT NOT NULL, FOREIGN KEY (store_id) REFERENCES sales.stores (store_id) ); Code language: SQL (Structured Query Language) (sql)

In this example:

Because we do not specify the name of the database explicitly in which the table is created

, the table of visits is created in the BikeStores database. For the scheme, we explicitly specify it, therefore the table of visits is created in the sales scheme.

The

visits table contains six columns:The

visit_id column

  • is the main key column of the table. IDENTITY(1,1) instructs SQL Server to automatically generate integers for the column starting from one and increasing by one for each new row.
  • The first_name and last_name columns are string columns with type VARCHAR. These columns can store up to 50 characters.
  • The visited_at is a DATETIME column that records the date and time the customer visits the store.

    • The phone column is a

  • variable string column that accepts NULL

    • .

  • The store_id column stores the identification numbers that identify the store where the customer visited

    • .

  • At the end of the table definition is a FOREIGN KEY constraint. This foreign key ensures that the values in column store_id of the visits table must be available in column store_id of the warehouse table. You will learn more about the FOREIGN KEY restriction in the following tutorial.

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

admin