SQL Primary Key

In SQL, a Primary Key is a field (or a combination of fields) that uniquely identifies each record in a table. Primary keys must contain unique values, and cannot contain NULL values. A table can have only one primary key, which may consist of single or multiple fields.

Here's how you can define a Primary Key:

1. While creating a table (Single column as Primary Key):

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

In this example, the ID field is the primary key of the "Employees" table.

2. While creating a table (Multiple columns as Primary Key):

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

In this example, the primary key is made up of two columns: OrderID and ProductID.

3. Adding Primary Key to an existing table:

You can add a primary key to an existing table using the ALTER TABLE statement. However, you must ensure that the column (or columns) you wish to set as the primary key contains unique values and no NULL values.

ALTER TABLE Employees
ADD PRIMARY KEY (ID);

In this example, we're setting ID as the primary key for the "Employees" table.

Notes:

The primary key uniquely identifies a record in the table.
Primary Key automatically creates a NOT NULL constraint on this column.
The primary key creates a CLUSTERED index on the column(s) by default in many databases.

The specific syntax for working with primary keys may vary slightly depending on the SQL dialect you're using (such as MySQL, PostgreSQL, SQL Server, etc.), so always check the specific documentation for your database system.

How to Define a Primary Key in SQL:
- Description: A Primary Key uniquely identifies each record in a table. It must be unique and not contain NULL values.
- Code Example:
```
CREATE TABLE example_table (
    id INT PRIMARY KEY,
    column1 VARCHAR(50),
    column2 INT
);
```
Composite Primary Key in SQL:
- Description: A Composite Primary Key consists of two or more columns, and the combination of values must be unique.
- Code Example:
```
CREATE TABLE example_table (
    id INT,
    category_id INT,
    PRIMARY KEY (id, category_id)
);
```
Adding Primary Key to Existing Table in SQL:
- Description: Use the ALTER TABLE statement to add a Primary Key to an existing table.
- Code Example:
```
ALTER TABLE existing_table
ADD PRIMARY KEY (existing_column);
```
Primary Key vs Unique Key in SQL:
- Description: Both ensure uniqueness, but a Primary Key is used to identify records, and it cannot contain NULL values. Unique Key allows NULL values.
- Code Example:
```
-- Primary Key
CREATE TABLE example_table (
    id INT PRIMARY KEY,
    column1 VARCHAR(50)
);

-- Unique Key
CREATE TABLE example_table (
    id INT UNIQUE,
    column1 VARCHAR(50)
);
```
Auto-increment Primary Key in SQL:
- Description: An Auto-increment Primary Key is a numeric value that automatically increments. Commonly used for surrogate keys.
- Code Example:
```
CREATE TABLE example_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    column1 VARCHAR(50)
);
```
Removing Primary Key Constraint in SQL:
- Description: Use ALTER TABLE to remove the Primary Key constraint from an existing table.
- Code Example:
```
ALTER TABLE existing_table
DROP PRIMARY KEY;
```

Common Errors with SQL Primary Key:

Common Error: Trying to insert duplicate values into a Primary Key column.

-- Error: Duplicate key violation
INSERT INTO example_table (id, column1) VALUES (1, 'Value');
INSERT INTO example_table (id, column1) VALUES (1, 'AnotherValue');

Common Error: Attempting to create a table without a Primary Key.

-- Error: Table must have a Primary Key
CREATE TABLE example_table (
    column1 VARCHAR(50),
    column2 INT
);