XanderNWA XanderNWA - 19 days ago 7
SQL Question

Display ALL the columns from table that doesn`t contain the Primary Keys

I`ve got this query to do.
Display ALL the columns from table named 'somehow' that are not primary keys.

This is how I am trying to obtain the column headers that differ from the Primary Key ID Column :

SELECT cols.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type <> 'PRIMARY KEY'
AND t.table_schema='mydb'
AND t.table_name='somehow'


Something is not right since I get an SQL Error. What am I doing wrong?

Update :

SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type <> 'PRIMARY'
AND t.table_schema='mydb'
AND t.table_name='somehow'


This shows exactly what I don`t want to get as a result (The Primary Key ) I need everything else to be shown :(

Answer

All table columns can be found in MySQL's system table columns. Its column_key field contains 'PRI' in case a column is part of the primary key. Hence:

select column_name
from information_schema.columns
where table_schema = 'mydb' 
   and table_name = 'somehow'
   and column_key <> 'PRI';
Comments