What is Primary Key in SQL server?
In SQL Server, a primary key is a column or a set of columns in a table that uniquely identifies each row. It is a constraint that ensures the uniqueness and integrity of data within the table.
Here are some key characteristics of a primary key in SQL Server:
- Uniqueness: A primary key constraint guarantees that each value in the primary key column(s) is unique. No two rows in the table can have the same values in the primary key column(s). This uniqueness allows for the identification and differentiation of individual rows.
- Non-nullability: By default, a primary key column cannot contain null values. It enforces that the primary key column(s) must have a valid value for each row. This ensures that the primary key can reliably identify and reference a specific record.
- Single or Composite Primary Key: A primary key can be a single column or a combination of multiple columns. In the case of a composite primary key, the uniqueness constraint applies to the combination of values in the specified columns. This allows for more complex and precise identification of rows.
- Table Identity: The primary key provides a unique identifier for each row in a table. It serves as an intrinsic attribute that distinguishes one record from another. This identity can be used to reference and access specific rows efficiently.
- Indexing: SQL Server automatically creates a clustered index on the primary key column(s) by default. This index organizes the data in the table based on the primary key, which improves query performance when searching, sorting, or joining data based on the primary key.
- Foreign Key References: Primary keys are often used as references in foreign key constraints to establish relationships between tables. The primary key column(s) in one table serve as the target for foreign key columns in other related tables. This allows for maintaining data integrity and enforcing referential constraints across tables.
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100)
);
In this example, the "EmployeeID" column is defined as the primary key. It ensures that each value in the "EmployeeID" column is unique and not null. The primary key constraint guarantees the uniqueness and integrity of the employee records in the "Employees" table, allowing for efficient identification and referencing of individual employees.