How to Find Out Whether a Table Has Some Unique Columns

How to find out whether a table has some unique columns

Here's an approach that is basically similar to @JNK's but instead of printing the counts it returns a ready answer for every column that tells you whether a column consists of unique values only or not:

DECLARE @table varchar(100), @sql varchar(max);
SET @table = 'some table name';

SELECT
  @sql = COALESCE(@sql + ', ', '') + ColumnExpression
FROM (
  SELECT
    ColumnExpression =
      'CASE COUNT(DISTINCT ' + COLUMN_NAME + ') ' +
      'WHEN COUNT(*) THEN ''UNIQUE'' ' +
      'ELSE '''' ' +
      'END AS ' + COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @table
) s

SET @sql = 'SELECT ' + @sql + ' FROM ' + @table;
PRINT @sql;  /* in case you want to have a look at the resulting query */
EXEC(@sql);

It simply compares COUNT(DISTINCT column) with COUNT(*) for every column. The result will be a table with a single row, where every column will contain the value UNIQUE for those columns that do not have duplicates, and empty string if duplicates are present.

But the above solution will work correctly only for those columns that do not have NULLs. It should be noted that SQL Server does not ignore NULLs when you want to create a unique constraint/index on a column. If a column contains just one NULL and all other values are unique, you can still create a unique constraint on the column (you cannot make it a primary key, though, which requires both uniquness of values and absence of NULLs).

Therefore you might need a more thorough analysis of the contents, which you could get with the following script:

DECLARE @table varchar(100), @sql varchar(max);
SET @table = 'some table name';

SELECT
  @sql = COALESCE(@sql + ', ', '') + ColumnExpression
FROM (
  SELECT
    ColumnExpression =
      'CASE COUNT(DISTINCT ' + COLUMN_NAME + ') ' +
      'WHEN COUNT(*) THEN ''UNIQUE'' ' +
      'WHEN COUNT(*) - 1 THEN ' +
        'CASE COUNT(DISTINCT ' + COLUMN_NAME + ') ' +
        'WHEN COUNT(' + COLUMN_NAME + ') THEN ''UNIQUE WITH SINGLE NULL'' ' +
        'ELSE '''' ' +
        'END ' +
      'WHEN COUNT(' + COLUMN_NAME + ') THEN ''UNIQUE with NULLs'' ' +
      'ELSE '''' ' +
      'END AS ' + COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = @table
) s

SET @sql = 'SELECT ' + @sql + ' FROM ' + @table;
PRINT @sql;  /* in case you still want to have a look at the resulting query */
EXEC(@sql);

This solution takes NULLs into account by checking three values: COUNT(DISTINCT column), COUNT(column) and COUNT(*). It displays the results similarly to the former solution, but the possible diagnoses for the columns are more diverse:

• UNIQUE means no duplicate values and no NULLs (can either be a PK or have a unique constraint/index);

• UNIQUE WITH SINGLE NULL – as can be guessed, no duplicates, but there's one NULL (cannot be a PK, but can have a unique constraint/index);

• UNIQUE with NULLs – no duplicates, two or more NULLs (in case you are on SQL Server 2008, you could have a conditional unique index for non-NULL values only);

• empty string – there are duplicates, possibly NULLs too.

SQL query to determine that values in a column are unique

Try this:

SELECT CASE WHEN count(distinct col1)= count(col1)
THEN 'column values are unique' ELSE 'column values are NOT unique' END
FROM tbl_name;

Note: This only works if 'col1' does not have the data type 'ntext' or 'text'. If you have one of these data types, use 'distinct CAST(col1 AS nvarchar(4000))' (or similar) instead of 'distinct col1'.

Check if column of a table has unique constraint

Rather than using the constraint name look for the same definition. something like

SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
        on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
where 
    tc.CONSTRAINT_TYPE = 'UNIQUE'
    and tc.TABLE_NAME = 'Product_Users'
    and cu.COLUMN_NAME = 'EmpID'

Find if a column has unique constraint

Both answers given here miss one way to enforce uniqueness on a column: by creating a unique index (without defining a unique constraint on the column). See these two links (one, two) if you are not familiar with this option.

This check should be performed additionally to the unique constraint check:

select count(*) from
USER_IND_COLUMNS cols
where cols.table_name='YOUR_TABLE_NAME'
and cols.COLUMN_NAME='YOUR_COLUMN';

To check for a unique constraint use the already provided method:

select count(*) cnt 
from user_constraints uc
where uc.table_name='YOUR_TABLE_NAME'
and uc.constraint_type='U';

Alternatively you can also look in the ALL_CONSTRAINTS and ALL_IND_COLUMNS views.

Check for uniqueness of column in postgres table

If all you want to do is verify that values are unique, then use a query:

select unique_no, count(*)
  from unique_test
 group by unique_no
having count(*) > 1;

If it needs to be boolean output:

select not exists (
  select unique_no, count(*)
    from unique_test
   group by unique_no
  having count(*) > 1
);

How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Query to determine if columns combine to create a unique key

Use the HAVING clause to easily identify duplicates.

select t.a, t.b, t.c, count(1) 
from my_table t    
group by t.a, t.b, t.c 
having count(1) > 1;

Crafting a SP to determine which columns make a table unique

It's not most appropriate task for stored procedure, but with time it's doable. Unfortunately, straightforward complete solution takes quite a bit of time, too much, I'd say. Time complexity is about O(2^C), where C is number of columns, and each try takes quite a bit of time as well.

The general idea for complete solution is to iterate over column sets, every possible combination of table columns (in no particular order inside set). Then we check set for coupling, and get one with least columns from ones passing the test.

Good news, there is quick test for possible solution. Check for count(distinct)=count(*). If it's ok, you can at least use whole table as one bit key...

Bad news, it's doesn't solve the problem.

But we can be greedy.

Count unique values for each column and sort columns in decreasing order. If there is simple 1-column PK, it will be in first column(s).

Now we get columns in this order and add to set of columns. If multiplication of unique values for selected column 1..K is less than count(*), you can't get unique index out of them - go further.

Once we count enough possibilities, try to check count(distinct) for select over these columns with dynamic SQL. If it's not enough, go further.

Once we get enough columns to cover uniqness, we are nearly done. Now let's try to remove some columns from this set. Iterate from 1 to K and try column set (1..I-1, I+1..K). If it's still unique, remove this column from set and continue dropping columns.

A bit tricky to continue iteration properly after removal of column, but it's doable.

Once we tried to remove all columns of covering set, we are done.

It is not precise algorythm, so you'll have to check it manually, but at least you'll have something to start with and it has O(C*D) time complexity where C is number of columns and D is amount of data in the table.

---

Related Topics