Mysql: Determine Table's Primary Key Dynamically

MySQL: Determine Table's Primary Key Dynamically

SHOW INDEX FROM <tablename>

You want the row where Key_name = PRIMARY

http://dev.mysql.com/doc/refman/5.0/en/show-index.html

You'll probably want to cache the results -- it takes a while to run SHOW statements on all the tables you might need to work with.

How to get primary key of table?

A better way is to use SHOW KEYS since you don't always have access to information_schema. The following works:

SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'

Column_name will contain the name of the primary key.

SQL query to find Primary Key of a table?

For Oracle, you can look it up in the ALL_CONSTRAINTS table:

SELECT a.COLUMN_NAME
FROM all_cons_columns a INNER JOIN all_constraints c 
     ON a.constraint_name = c.constraint_name 
WHERE c.table_name = 'TBL'
  AND c.constraint_type = 'P';

DEMO.

For SQL Server, it was already answered here, and for MySQL check @ajon's answer.

Adding primary key to some exising tables in a database

This query, will exclude all tables that have either a primary key or an identity column, it will then add identity column and primary key on the remaining objects

DECLARE @PKScript AS VARCHAR(max) = '';
SELECT @PKScript += 
    '  ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(obj.SCHEMA_ID))+'.'+ QUOTENAME(obj.name) + 
                      ' ADD PK_ID BIGINT IDENTITY;' +
     ' ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(obj.SCHEMA_ID))+'.'+QUOTENAME(obj.name) + 
                     ' ADD CONSTRAINT PK_ID_' + obj.name+ ' PRIMARY KEY NONCLUSTERED (PK_ID) '
 FROM sys.objects obj
 WHERE object_id not in
     (select parent_object_id
      from sys.key_constraints 
      where type = 'PK'
      UNION
      Select object_id
      from sys.identity_columns
      )
      AND type = 'U'
--PRINT (@PKScript);
EXEC(@PKScript);  

For the tables that already have identity column defined, you might use this query to set this identity column to be the primary key (because you can't have two identity columns on the same table)

DECLARE @PKScript2 VARCHAR(max)='';

SELECT @PKScript2 += ' ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(obj.SCHEMA_ID))+'.'+
       QUOTENAME(obj.name) + ' ADD CONSTRAINT PK_' + icol.name +'_'+ obj.name+
       ' PRIMARY KEY NONCLUSTERED (' + QUOTENAME(icol.name) + ')' + CHAR(13)
FROM sys.identity_columns icol INNER JOIN 
     sys.objects obj on icol.object_id= obj.object_id
WHERE NOT EXISTS (SELECT * FROM sys.key_constraints k
                  WHERE k.parent_object_id = obj.object_id
                        AND k.type = 'PK')
      AND obj.type = 'U'
--PRINT (@PKScript2);
EXEC(@PKScript2);   

---

Related Topics