Do you have to find the unique key for a heap of data, so you can set the primary key for the table? Or do you have to find the existing unique constraint on an existing table?
i have a table which don't have any primary key or any index on it. I want to know wthere there is any unique key on the table.
The quick-and-dirty way is to add a unique constraint to the table in Enterprise Manager, and just test to see if it is accepted. For a more serious approach, you need to run some queries. First, get the total number of rows in the table: SELECT COUNT(*) FROM MyTable Next, determine the number of distinct values per column: SELECT COUNT(DISTINCT Column1) FROM MyTable SELECT COUNT(DISTINCT Column2) FROM MyTable etc. If the count for one of the columns is identical to the total number of rows, then you have the unique key. But if there is no single column that is a unique key, then you have to run queries for combinations of columns: SELECT COUNT(*) FROM (SELECT DISTINCT Column1, Column2 FROM MyTable) X SELECT COUNT(*) FROM (SELECT DISTINCT Column1, Column3 FROM MyTable) X SELECT COUNT(*) FROM (SELECT DISTINCT Column2, Column3 FROM MyTable) X etc. You may need to add more columns: SELECT COUNT(*) FROM (SELECT DISTINCT Column1, Column2, Column3 FROM MyTable) X etc. If you cannot find any key this way, there must be duplicate rows in the table.
or if you want to know if a table has any unique constraints, then select OBJECTPROPERTY ( object_id('table_name'),'TableHasUniqueCnst') If the result is 1 then it has
[quote user="thugs"] Thanks for the replies [/quote] I am interested to know which method you are going to use []
