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?
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.
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.
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.
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'
Something like this might work (untested):
SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
WHERE TC.TABLE_NAME = 'MYTABLENAME'
AND TC.CONSTRAINT_TYPE = 'UNIQUE'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With