Retrieving Column Information (Composite Key) in Sql

Retrieving column information (composite key) in SQL

In SQL Server you can do

SELECT K.TABLE_CATALOG, 
       K.TABLE_NAME, 
       K.COLUMN_NAME, 
       K.ORDINAL_POSITION 
FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE K 
       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
         ON K.TABLE_CATALOG = TC.TABLE_CATALOG 
            AND K.TABLE_SCHEMA = TC.TABLE_SCHEMA 
            AND K.CONSTRAINT_NAME = TC.CONSTRAINT_NAME 
WHERE  TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 

or

SELECT Object_name(C.OBJECT_ID) TABLE_NAME, 
       C.NAME, 
       IC.INDEX_COLUMN_ID 
FROM   SYS.KEY_CONSTRAINTS K 
       INNER JOIN SYS.INDEX_COLUMNS IC 
         ON K.PARENT_OBJECT_ID = IC.OBJECT_ID 
            AND K.UNIQUE_INDEX_ID = IC.INDEX_ID 
       INNER JOIN SYS.COLUMNS C 
         ON IC.OBJECT_ID = C.OBJECT_ID 
            AND IC.COLUMN_ID = C.COLUMN_ID 
WHERE  K.TYPE = 'PK' 

IN ORACLE

SELECT K.OWNER, 
       K.TABLE_NAME, 
       K.INDEX_NAME, 
       C.COLUMN_NAME, 
       C.COLUMN_POSITION 
FROM   ALL_CONSTRAINTS K 
       INNER JOIN ALL_IND_COLUMNS C 
         ON K.OWNER = C.INDEX_OWNER 
            AND K.TABLE_NAME = C.TABLE_NAME 
            AND K.INDEX_NAME = C.INDEX_NAME 
WHERE  K.CONSTRAINT_TYPE = 'P' 

Accessing data with use of composite key

As per @spencer7593 comments to his answer. It appears my goal cannot be achieved in this manner. Each of primary keys columns must be addressed individually.

@spencer7593 clarifying comment:

@Rob: no, not in the context of a MySQL query to retrieve a row based on the value of the columns in the primary key, no – spencer7593 Jun 14 at 15:25

How to get a list of tables with composite primary key in SQL Server?

You can dig that info up in information_schema.table_constraints and information_schema.constraint_column_usage tables, by checking for multiple rows of PRIMARY KEY constraints on a table, something like:

SELECT col.table_name 
FROM information_schema.table_constraints tc 
JOIN information_schema.constraint_column_usage col
  ON col.constraint_name = tc.constraint_name
 AND col.table_name = tc.table_name
 AND tc.constraint_type = 'PRIMARY KEY'
GROUP BY col.table_name
HAVING COUNT(*) > 1

An SQLfiddle to test with.

SQL Server : retrieve composite primary key after insert

@@IDENTITY retrieves the last inserted identity value (subject to certain conditions about scoping). This is quite clearly documented here. So, if you have no column defined as an identity then nothing will be returned. This really has nothing to do with primary keys, except for the fact that it is good practice to use identity columns as primary keys.

If you want information about the last records inserted (or updated or deleted), then learn to use the output clause. This is the best way to get the data that was actually inserted (or updated or deleted) in the given statement.

Here is the documentation on the output clause.

WHERE_IN query with a composite key?

This syntax works for Oracle and PostgreSQL:

SELECT *
  FROM table_name
 WHERE (key_part_1, key_part_2) IN ( ('B',1), ('C',2) );

where clause with composite primary key

You can compare tuples with other operators than just = or IN

So

where (id, age)  < (3, 22)

is valid SQL.

But I have to admit that I find that condition highly confusing given the column names chosen

---

Related Topics