Creating tables in SQL Server is a fundamental task in database design. SQL Server provides Transact-SQL (T-SQL) as a programming language to create and manage database objects. In T-SQL, you can create a table using the SQL CREATE TABLE statement followed by the table name and column definitions. Creating a table with primary and foreign keys is a common task in SQL Server database design.

Syntax

Here is the basic syntax to create a table:

CREATE TABLE table_name
(
column1 datatype1,
column2 datatype2,
column3 datatype3,
...
);

Example

For example, to create a table named Customers with columns for CustomerID, FirstName, LastName, Email, and PhoneNumber, you can use the following code:

CREATE TABLE Customers
(
CustomerID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
PhoneNumber VARCHAR(20)
);

To add constraints to the table, such as primary keys and foreign keys, you can modify the column definitions with additional keywords.

A PRIMARY KEY is a unique identifier for each row in a table, which ensures that each row can be uniquely identified. In SQL, you can add a SQL primary key to a table using the PRIMARY KEY constraint.

Here's an example of how to create a table named Orders with a primary key constraint on the OrderID column:

CREATE TABLE Orders
(
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
TotalAmount MONEY
);

In this example, the OrderID column is set as the primary key for the Orders table.

A FOREIGN KEY is a constraint that establishes a link between two tables based on the values of a column in each table. In SQL, you can add a foreign key to a table using the FOREIGN KEY constraint.

Here's an example of how to create a table named OrderDetails with a foreign key constraint on the OrderID column, which references the Orders table:

CREATE TABLE OrderDetails
(
OrderDetailID INT PRIMARY KEY,
OrderID INT,
ProductID INT,
Quantity INT,
Price MONEY,
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

In this example, the OrderID column in the OrderDetails table is defined as a foreign key that references the OrderID column in the Orders table. This ensures that each row in the OrderDetails table corresponds to a valid order in the Orders table.