Get List of Primary Key Columns in Snowflake

Question

I have a composite primary key. In Information_Schema.Table_Constraints I can see 1 record for this key but it does not have information on columns which make this primary key.

where can i find list of columns for constraints?

Answer 1

You can use the SHOW PRIMARY KEYS command to find the columns that make up a given primary key. For example:

CREATE OR REPLACE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b));
SHOW PRIMARY KEYS IN TABLE t1;

created_on  database_name   schema_name table_name  column_name key_sequence    constraint_name comment
2020-09-24 13:48:50.208 -0700   ANDREW_DB   PUBLIC  T1  A   1   SYS_CONSTRAINT_159d987b-8160-458e-bad1-50abda169c16 
2020-09-24 13:48:50.208 -0700   ANDREW_DB   PUBLIC  T1  B   2   SYS_CONSTRAINT_159d987b-8160-458e-bad1-50abda169c16 

You can also view primary keys for all tables in a given context (that you have access to) with these commands:

SHOW PRIMARY KEYS IN SCHEMA;
SHOW PRIMARY KEYS IN DATABASE;
SHOW PRIMARY KEYS IN ACCOUNT;

Answer 2

SHOW PRIMARY KEYS IN DATABASE TEST_DB; SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Answer 3

I have a customer who needed to get the composite primary keys for their tables returned as rows in a result set. This is the User Defined Table Function (UDTF) I wrote for them. If you don't want it as a table, the code is easy enough to modify to return as an array, JSON, etc.

/**************************************************************************************************************
*                                                                                                             *
*  Set up test tables with four types of primary key: Named composite, unnamed composite, inline, and none.   * 
*                                                                                                             *
**************************************************************************************************************/
 
-- Named multi-column PK
create or replace temporary table table1
(
column_name1 number NOT NULL,
column_name2 number NOT NULL,
column_name3 string,
CONSTRAINT Constraint_name PRIMARY KEY (column_name1, column_name2)
);
 
-- Unnamed multi-column PK
create temporary table table2
(
column_name1 number NOT NULL,
column_name2 number NOT NULL,
column_name3 string,
PRIMARY KEY (column_name1, column_name2)
);
 
-- Inline single-column PK
create or replace temporary table table3
(
column_name1 number primary key,
column_name2 number NOT NULL,
column_name3 string
);
 
-- No PK defined
create or replace temporary table table4
(
column_name1 number,
column_name2 number,
column_name3 string
);
 
/********************************************************************************************************
*                                                                                                       *
* User defined table function (UDTF) to get primary keys for a table.                                   *
*                                                                                                       *
* @param  {string}:  TABLE_DDL    The DDL for the table to get the PKs. Usually use get_ddl.            *
* @return {table}:                A table with the columns comprising the table's primary key           *
*                                                                                                       *
********************************************************************************************************/
create or replace function GET_PK_COLUMNS(TABLE_DDL string)
returns table (PK_COLUMN string)
language javascript
as
$$
{
    processRow: function get_params(row, rowWriter, context){
 
        var pkCols = getPKs(row.TABLE_DDL);
        for (i = 0; i < pkCols.length; i++) {
            rowWriter.writeRow({PK_COLUMN: pkCols[i] }); 
        }
         
        function getPKs(tableDDL) {
            var keyword = "primary key";
            var ins = -1;
            var s = tableDDL.split("\n");
            for (var i = 0; i < s.length; i++) {  
                ins = s[i].indexOf(keyword);
                if (ins != -1) {
                    var colList = s[i].substring(ins + keyword.length);
                    colList = colList.replace("(", "");
                    colList = colList.replace(")", "");
                    var colArray = colList.split(",");
                    for (c = 0; c < colArray.length; c++) {
                        colArray[c] = colArray[c].trim();
                    }
                    return colArray;
                }
            }
            return [];  // No PK
        }
    }
}
$$;
 
/**************************************************************************************************************
*                                                                                                             *
*  Test execution of the UDTF.                                                                                * 
*                                                                                                             *
**************************************************************************************************************/
 
select * from table(get_pk_columns(get_ddl('table', 'table1'))) PKS;  -- Multi-column PK with named constraint
select * from table(get_pk_columns(get_ddl('table', 'table2'))) PKS;  -- Multi-column PK with no name for constraint
select * from table(get_pk_columns(get_ddl('table', 'table3'))) PKS;  -- Single column PK inline definition
select * from table(get_pk_columns(get_ddl('table', 'table4'))) PKS;  -- No PKs