Create Table in SQL Server

The aim of this article is to create TablePerson and TableGender tables and establish primary key and foreign key constraints. In SQL Server, tables can be created graphically using SQL Server Management Studio (SSMS) or using a query.

To create TablePerson table, graphically, using SQL Server Management Studio

• Right click on Tables folder in Object explorer window
• Select New
• Select Table
• Fill Column Name, Data Type and Allow Nulls, as shown below and save the table as TablePerson.

Type all column names and respective data types, specify which columns can take null values

Save table

Type table name and press OK.

Follow the below image to see the data inside the TablePerson. It will contain no value because we not inserted any rows yet.

The following statement creates TableGender table, with GengerID and Gender columns. The following statement creates TableGender table, with GengerID and Gender columns. GenderID column, is the primary key column. The primary key is used to uniquely identify each row in a table. Primary key does not allow nulls.

Code:

<span class="pln">
</span><span class="typ">Create</span><span class="pln"> </span><span class="typ">Table</span><span class="pln"> </span><span class="typ">TableGender</span><span class="pun">(</span><span class="pln">
</span><span class="typ">GenderID</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="typ">Not</span><span class="pln"> </span><span class="typ">Null</span><span class="pln"> </span><span class="typ">Primary</span><span class="pln"> </span><span class="typ">Key</span><span class="pun">,</span><span class="pln">
</span><span class="typ">Gender</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">)</span><span class="pln">
</span><span class="pun">)</span><span class="pln">
</span>

<span class="pln">
 </span><span class="typ">Commands</span><span class="pln"> completed successfully</span><span class="pun">.</span><span class="pln">
</span>

Code: To add a foreign key reference using a query

<span class="pln">
</span><span class="typ">Alter</span><span class="pln"> table </span><span class="typ">TablePerson</span><span class="pln"> 
</span><span class="kwd">add</span><span class="pln"> constraint </span><span class="typ">TablePerson_GenderId_FK</span><span class="pln"> FOREIGN KEY </span><span class="pun">(</span><span class="typ">GenderID</span><span class="pun">)</span><span class="pln"> references </span><span class="typ">TableGender</span><span class="pun">(</span><span class="pln">ID</span><span class="pun">)</span><span class="pln">
</span>

Syntax: The general formula is here

<span class="pln">

</span><span class="typ">Alter</span><span class="pln"> table </span><span class="typ">ForeignKeyTable</span><span class="pln"> </span><span class="kwd">add</span><span class="pln"> constraint </span><span class="typ">ForeignKeyTable_ForiegnKeyColumn_FK</span><span class="pln"> 
FOREIGN KEY </span><span class="pun">(</span><span class="typ">ForiegnKeyColumn</span><span class="pun">)</span><span class="pln"> references </span><span class="typ">PrimaryKeyTable</span><span class="pln"> </span><span class="pun">(</span><span class="typ">PrimaryKeyColumn</span><span class="pun">)</span><span class="pln">
</span>

Foreign keys are used to enforce database integrity. In layman's terms, A foreign key in one table points to a primary key in another table. The foreign key constraint prevents invalid data form being inserted into the foreign key column. The values that you enter into the foreign key column, has to be one of the values contained in the table it points to.

To graphically add a foreign key reference

• Right click TablePerson table and select Design.
• In the table design window, right click on GenderId column and select Relationships
• In the Foreign Key Relationships window, click Add button
• Now expand, in Tables and Column Specification row, by clicking the, + sign
• Click on the elipses button, that is present in Tables and Column Specification row
• From the Primary Key Table, dropdownlist, select tblGender
• Click on the row below, and select ID column
• From the column on the right hand side, select GenderID
• Click OK and then click close.
• Finally save the table.