A primary key is a special column (or set of combined columns) in a relational database table, that is used to uniquely identify each record. A database primary key cannot contain NULL values and each table can have only one primary key. A primary key can either be a single column, or a combined set of columns.
Benefits of Primary Key in a Database
The benefits of using primary keys in relational databases are many. The list below, summarizes the main benefits:
- Database operations like searching for records are faster because an index is used, based on the primary key.
- Using the primary key, you can easily identify and find unique rows in the database table.
- They allow you to update/delete only specific records by uniquely identifying them.
- The table’s data is sorted based on the primary key.
- They help you avoid inserting duplicate records in a table.
- The allow creating referential integrity constraints (i.e. use of foreign keys).
Examples of Primary Keys in SQL Server Database
Below, you can find some simple examples of creating tables with primary keys in SQL Server databases.
SQL Server Table with Primary Key – No Auto-Increment
CREATE TABLE tblTest1 ( id INT PRIMARY KEY, code VARCHAR(50) ); GO
In the above example, the T-SQL code creates a SQL Server database table, along with setting the column “ID” as primary key. However, you will still need to include the value for the “ID” column in your INSERT statements, since auto-increment was not specified.
The second example, specifies auto-increment for the primary key.
SQL Server Table with Primary Key – With Auto-increment
CREATE TABLE tblTest2 ( id INT PRIMARY KEY IDENTITY(1,1), code VARCHAR(50) ); GO
As you can see, in this second example, the T-SQL code creates a SQL Server database table, along with setting the column “ID” as primary key and also, it uses the IDENTITY property, in order for the primary key value, to be automatically set after each INSERT statement.
See more examples of the CREATE TABLE statement in SQL Server, in this MS Docs article.
- How to Get Started with SQL Server
- What is SQL Server Management Studio?
- What are T-SQL Snippets in SSMS?
- All our articles on databases.
Featured Database Software Tool
Create and modify T-SQL code snippets for use in SQL Server Management Studio, fast, easy and efficiently.Learn More
Rate this article:
Reference: TechHowTos.com (https://www.techhowtos.com)
© 2018 TechHowTos.com