Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL - How to get the Unique Key's Column Name from Table

Tags:

sql

I know how to get the columns from a table using the following SQL statement:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE (TABLE_NAME = 'MYTABLENAME')

But how do I just return what the UNIQUE Key's Column name?

like image 983
eqiz Avatar asked Jul 05 '10 14:07

eqiz


People also ask

How do I get unique column names in SQL?

To get unique or distinct values of a column in MySQL Table, use the following SQL Query. SELECT DISTINCT(column_name) FROM your_table_name; You can select distinct values for one or more columns. The column names has to be separated with comma.

How do I find a unique column in a table?

First we write ALTER TABLE, then we list the name of the table (in our example: product ), and next we add the clause ADD CONSTRAINT with the name of the unique constraint (in our example: UQ_product_name ). This is followed by the UNIQUE keyword with column/columns (in our example it is column: name ) in parentheses.

How can I get unique records from a table in SQL?

The SQL SELECT DISTINCT Statement The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.


2 Answers

select CCU.CONSTRAINT_NAME, CCU.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as CCU
    on TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
    and TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
    and TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
where TC.CONSTRAINT_CATALOG = 'MyCatalogName'
and TC.CONSTRAINT_SCHEMA = 'MySchemaName'
and TC.TABLE_NAME = 'MyTableName'
and TC.CONSTRAINT_TYPE = 'UNIQUE'

Bear in mind that a table may have multiple unique constraints, each containing multiple columns. You will need to apply some additional logic to select the right one.

UPDATE - based on other comments...

The above query will find all UNIQUE key constraints. However, it will not find PRIMARY KEY constraints, or UNIQUE indexes that were created outside a UNIQUE key constraint.

To find the primary key, replace the last line with:

and TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
like image 90
Christian Hayter Avatar answered Sep 17 '22 15:09

Christian Hayter


Something like this might work (untested):

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
WHERE TC.TABLE_NAME = 'MYTABLENAME'
AND TC.CONSTRAINT_TYPE = 'UNIQUE'
like image 36
Donald Byrd Avatar answered Sep 20 '22 15:09

Donald Byrd