WikiGalaxy

Personalize

SQL Primary Key

Understanding Primary Keys:

A primary key is a field in a table, which uniquely identifies each row/record in that table. Primary keys must contain unique values, and cannot contain NULLs.

Characteristics of Primary Keys:

Primary keys enforce entity integrity by uniquely identifying entity instances. They are usually a single column, but can also be a combination of columns (composite key).

Creating a Primary Key:

When creating a table, you can define a primary key using the PRIMARY KEY constraint. This can be done at the column level or at the table level.

Benefits of Using Primary Keys:

Primary keys help maintain data integrity and ensure that each record can be uniquely identified. They are essential for establishing relationships between tables.


CREATE TABLE Employees (
  EmployeeID int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  PRIMARY KEY (EmployeeID)
);

Composite Primary Keys:

A composite key is a primary key that consists of two or more columns. It is used when a single column cannot uniquely identify records.

Example of Composite Key:

In a table where both OrderID and ProductID together uniquely identify a record, a composite key can be used.


CREATE TABLE OrderDetails (
  OrderID int NOT NULL,
  ProductID int NOT NULL,
  Quantity int,
  PRIMARY KEY (OrderID, ProductID)
);

Console Output:

Table created successfully with primary key constraints.

Primary Key Constraints

Enforcing Uniqueness:

A primary key constraint ensures that no two rows have the same value in the specified column(s). This is crucial for maintaining uniqueness across the dataset.

Automatic Index Creation:

When you define a primary key, most database systems automatically create an index for it, which helps speed up query performance.


ALTER TABLE Employees
ADD CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID);

Dropping a Primary Key:

To drop a primary key constraint, use the ALTER TABLE statement followed by DROP CONSTRAINT.


ALTER TABLE Employees
DROP CONSTRAINT PK_Employee;

Console Output:

Primary key constraint added and removed successfully.

Primary Key vs. Unique Key

Primary Key:

A primary key uniquely identifies each record in a table and does not allow NULL values.

Unique Key:

A unique key also ensures uniqueness for a column or set of columns, but it allows one NULL value per column.


CREATE TABLE Products (
  ProductID int NOT NULL,
  SerialNumber varchar(255) UNIQUE,
  PRIMARY KEY (ProductID)
);

Console Output:

Table with primary and unique keys created successfully.

Primary Key Best Practices

Choosing the Right Column:

Select a column that is unlikely to change and will always have unique values. Avoid using columns that may change frequently.

Avoiding Complex Keys:

Use simple, single-column primary keys whenever possible. Complex keys can lead to performance issues and increased complexity.


CREATE TABLE Customers (
  CustomerID int NOT NULL,
  Email varchar(255) NOT NULL UNIQUE,
  PRIMARY KEY (CustomerID)
);

Console Output:

Customer table created with best practice primary key.

Primary Key Constraints in Action

Understanding Constraints:

Constraints are rules enforced on data columns in a table. Primary key constraints ensure uniqueness and integrity.

Implementing Constraints:

Constraints can be defined when creating a table or added later using the ALTER TABLE statement.


ALTER TABLE Orders
ADD CONSTRAINT PK_Order PRIMARY KEY (OrderID);

Console Output:

Primary key constraint applied to Orders table.

Primary Key in Relational Databases

Role in Relationships:

Primary keys play a vital role in establishing relationships between tables in a relational database.

Foreign Key References:

A foreign key in one table points to a primary key in another table, creating a relationship between the two.


CREATE TABLE Orders (
  OrderID int NOT NULL,
  CustomerID int,
  PRIMARY KEY (OrderID),
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Console Output:

Orders table created with primary and foreign key constraints.

Primary Key in Data Integrity

Ensuring Data Integrity:

Primary keys are fundamental in ensuring data integrity by preventing duplicate records and maintaining unique identifiers.

Data Consistency:

By ensuring each record is unique, primary keys help maintain consistent and reliable data across the database.


CREATE TABLE Invoices (
  InvoiceID int NOT NULL,
  InvoiceNumber varchar(255) NOT NULL UNIQUE,
  PRIMARY KEY (InvoiceID)
);

Console Output:

Invoices table created with data integrity measures.

Primary Key Design Considerations

Choosing Key Columns:

Select columns that are stable and will not change over time. Avoid using data that may be updated frequently.

Impact on Performance:

Well-chosen primary keys can improve database performance by optimizing indexing and query execution.


CREATE TABLE Suppliers (
  SupplierID int NOT NULL,
  SupplierCode varchar(50) NOT NULL UNIQUE,
  PRIMARY KEY (SupplierID)
);

Console Output:

Suppliers table created with optimized primary key design.