5 Ways to Return UNIQUE Constraints in SQL Server

In SQL Server, we can use UNIQUE constraints to ensure that a column (or columns) contain only unique values. When we have a UNIQUE constraint against a column, the system will prevent any duplicate values are entered into that column.

Sometimes we need to return a list of UNIQUE constraints, so that we simply know what we’re working with. Other times we might want to create scripts for all of our UNIQUE constraints so that we can recreate the constraints later.

Regardless of the reason, here are five methods for returning UNIQUE constraints in a SQL Server database.

The sys.key_constraints View

One of the most direct ways to find UNIQUE constraints is by using the sys.key_constraints view. This view is specifically designed to provide information about UNIQUE and PRIMARY KEY constraints. We can narrow it down to just UNIQUE constraints by filtering it to just those of the UQ type (UQ = UNIQUE).

So we can use a quick query like this:

SELECT * FROM sys.key_constraints
WHERE type = 'UQ';

Here’s a slightly modified version that returns less columns:

SELECT
    SCHEMA_NAME(schema_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    name AS ConstraintName
FROM sys.key_constraints
WHERE type = 'UQ';

Or if we want to get fancy:

SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    kc.name AS ConstraintName,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS ColumnNames
FROM 
    sys.key_constraints kc
INNER JOIN 
    sys.tables t ON kc.parent_object_id = t.object_id
INNER JOIN 
    sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    kc.type = 'UQ'
GROUP BY
    t.schema_id, t.name, kc.name
ORDER BY 
    SchemaName, TableName, ConstraintName;

That last one includes a list of columns that the constraint applies to, which could be handy to know.

Other System Views

We can also query other system views without even touching the sys.key_constraints view.

Example:

SELECT 
    OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName,
    o.name AS TableName,
    i.name AS ConstraintName,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS ColumnNames
FROM 
    sys.indexes i
INNER JOIN 
    sys.objects o ON i.object_id = o.object_id
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    i.is_unique = 1
    AND i.is_unique_constraint = 1
    AND o.type = 'U'
GROUP BY
    o.object_id, o.name, i.name
ORDER BY 
    SchemaName, TableName, ConstraintName;

This query provides us with the schema name, table name, constraint name, and the columns involved in each UNIQUE constraint.

Using INFORMATION_SCHEMA Views

Another option is to use the INFORMATION_SCHEMA views, which can be more portable across different database systems:

SELECT 
    tc.TABLE_SCHEMA AS SchemaName,
    tc.TABLE_NAME AS TableName,
    tc.CONSTRAINT_NAME AS ConstraintName,
    STRING_AGG(cu.COLUMN_NAME, ', ') AS ColumnNames
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
    ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
    AND tc.TABLE_SCHEMA = cu.TABLE_SCHEMA
    AND tc.TABLE_NAME = cu.TABLE_NAME
WHERE 
    tc.CONSTRAINT_TYPE = 'UNIQUE'
GROUP BY
    tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_NAME
ORDER BY 
    SchemaName, TableName, ConstraintName;

This query achieves similar results to the previous example, but uses the INFORMATION_SCHEMA views instead.

The sp_help Stored Procedure

For a quick check on a specific table, we can use the sp_help stored procedure:

EXEC sp_help 'Application.Countries';

This will return multiple result sets covering a bunch of stuff, including one that shows all indexes and one that shows all constraints on the specified table.

Scripting UNIQUE Constraints

If we need to script out our UNIQUE constraints for documentation or migration purposes, we can use a query like this:

SELECT 
    OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName,
    o.name AS TableName,
    i.name AS ConstraintName,
    'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(o.name) + 
    ' ADD CONSTRAINT ' + QUOTENAME(i.name) + ' UNIQUE (' + 
    STRING_AGG(QUOTENAME(c.name), ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) + ');' AS ScriptText
FROM 
    sys.indexes i
INNER JOIN 
    sys.objects o ON i.object_id = o.object_id
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    i.is_unique = 1
    AND i.is_unique_constraint = 1
    AND o.type = 'U'
GROUP BY
    o.object_id, o.name, i.name
ORDER BY 
    SchemaName, TableName, ConstraintName;

This query generates ALTER TABLE statements that we can use to recreate our UNIQUE constraints.

Primary Keys?

Need to find the primary keys? Here are 11 Ways to Retrieve a Primary Key in SQL Server.