SQL-script: How to write ALTER statements to set Primary key on an existing table?

SQL Script: Setting a Primary Key on an Existing Table

Learn how to add a primary key constraint to an existing SQL table using ALTER TABLE statements in SQL Server, ensuring data integrity and optimizing performance.

Adding a primary key to an existing table is a fundamental database operation that ensures data integrity and often improves query performance. A primary key uniquely identifies each record in a table and prevents duplicate rows. This article will guide you through the process of adding a primary key to an existing table in SQL Server using ALTER TABLE statements, covering various scenarios and best practices.

Understanding Primary Keys and Their Importance

A primary key is a column or a set of columns that uniquely identifies each row in a database table. It enforces entity integrity, meaning that each row in the table is unique and can be referenced unambiguously. Primary keys are crucial for:

• Data Integrity: Preventing duplicate records and ensuring that every row has a unique identifier.
• Relationships: Serving as the target key for foreign key constraints, establishing relationships between tables.
• Performance: Often indexed automatically, which speeds up data retrieval operations, especially when joining tables.

Before adding a primary key, it's essential to ensure that the chosen column(s) contain unique, non-NULL values for all existing rows. If not, you'll need to clean your data first.

flowchart TD
    A[Start: Identify Table and Column(s)] --> B{Are chosen columns unique and non-NULL?}
    B -- No --> C[Clean/Update Data to Ensure Uniqueness/Non-NULL]
    C --> B
    B -- Yes --> D[Check for Existing Primary Key]
    D -- Yes --> E[Drop Existing Primary Key (if necessary)]
    E --> F[Add New Primary Key Constraint]
    D -- No --> F
    F --> G[End: Primary Key Added]

Workflow for Adding a Primary Key to an Existing Table

Adding a Single-Column Primary Key

The most common scenario is adding a primary key to a single column. This is straightforward if the column already contains unique, non-NULL values. If the column allows NULLs, you'll first need to modify the column to be NOT NULL.

USE YourDatabaseName;
GO

-- Step 1: Ensure the column does not allow NULLs (if it currently does)
ALTER TABLE YourTableName
ALTER COLUMN YourColumnName INT NOT NULL;
GO

-- Step 2: Add the PRIMARY KEY constraint
ALTER TABLE YourTableName
ADD CONSTRAINT PK_YourTableName_YourColumnName PRIMARY KEY (YourColumnName);
GO

Adding a single-column primary key to an existing table

Adding a Composite Primary Key

A composite primary key consists of two or more columns whose values, when combined, uniquely identify each row in the table. This is useful when no single column can guarantee uniqueness. Similar to a single-column primary key, all columns involved in the composite key must be NOT NULL.

USE YourDatabaseName;
GO

-- Step 1: Ensure all columns in the composite key do not allow NULLs
ALTER TABLE YourTableName
ALTER COLUMN Column1 INT NOT NULL;

ALTER TABLE YourTableName
ALTER COLUMN Column2 VARCHAR(50) NOT NULL;
GO

-- Step 2: Add the COMPOSITE PRIMARY KEY constraint
ALTER TABLE YourTableName
ADD CONSTRAINT PK_YourTableName_Composite PRIMARY KEY (Column1, Column2);
GO

Adding a composite primary key to an existing table

Handling Existing Duplicate or NULL Values

If your chosen column(s) contain duplicate values or NULLs, you cannot directly add a primary key. You must first resolve these data integrity issues. Here's a common approach:

1. Identify Duplicates/NULLs

Use GROUP BY and HAVING COUNT(*) > 1 to find duplicate values, and WHERE YourColumnName IS NULL to find NULLs.

2. Backup Data

Before making any changes, always back up your table or database. This is a critical step to prevent data loss.

3. Clean Data

Decide how to handle duplicates (e.g., delete redundant rows, update to unique values) and NULLs (e.g., update to a default value, generate new unique identifiers).

4. Add Primary Key

Once the data is clean and meets the primary key requirements, proceed with the ALTER TABLE ADD CONSTRAINT PRIMARY KEY statement as shown above.

-- Example: Finding duplicate values in a column
SELECT YourColumnName, COUNT(YourColumnName)
FROM YourTableName
GROUP BY YourColumnName
HAVING COUNT(YourColumnName) > 1;

-- Example: Finding NULL values in a column
SELECT * FROM YourTableName
WHERE YourColumnName IS NULL;

-- Example: Deleting duplicate rows (keep one, delete others)
WITH CTE AS (
    SELECT YourColumnName, OtherColumn, 
           ROW_NUMBER() OVER (PARTITION BY YourColumnName ORDER BY OtherColumn) as rn
    FROM YourTableName
)
DELETE FROM CTE WHERE rn > 1;

-- Example: Updating NULLs to a default or generated value
UPDATE YourTableName
SET YourColumnName = ISNULL(YourColumnName, NEWID()) -- For GUIDs
WHERE YourColumnName IS NULL;

-- Or for integers, you might need a more complex logic or temporary column
-- UPDATE YourTableName SET YourColumnName = (SELECT MAX(YourColumnName) FROM YourTableName) + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) WHERE YourColumnName IS NULL;

SQL queries to identify and resolve data integrity issues

Dropping an Existing Primary Key

In some cases, you might need to change an existing primary key. This involves dropping the old one before adding a new one. You'll need the name of the existing primary key constraint, which can often be found in your database's system views.

USE YourDatabaseName;
GO

-- Step 1: Find the name of the existing primary key constraint
SELECT name AS PrimaryKeyConstraintName
FROM sys.key_constraints
WHERE type = 'PK' AND parent_object_id = OBJECT_ID('YourTableName');
GO

-- Step 2: Drop the existing primary key constraint
ALTER TABLE YourTableName
DROP CONSTRAINT PK_ExistingPrimaryKeyName;
GO

-- Step 3: Add the new primary key (refer to previous examples)

Dropping an existing primary key constraint