Mysql – the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database

constraintforeign keyMySQLprimary-key

I have a mysql database that has 250 tables.
I would like to run a query to retrieve:
tableName ; FieldName ; ConstaintName (Primary or foreign key).

I could use information_schema.TABLE_CONSTRAINTS to retrieve the constarint type
but for the inner join I have no idea what should I use.

What is the best approach to achieve this task?

EDIT:
not Constraint name. Should be CONSTRAINT_TYPE instead of ConstaintName. So we should be able to see (Primary or foreign key) only.

Best Answer

You can use INFORMATION_SCHEMA KEY_COLUMN_USAGE table.

SELECT
table_name AS TableName,
column_name AS ColumnName,
Constraint_name as ConstraintName
FROM
information_schema.key_column_usage
WHERE table_schema = 'database_name'

Updated code based on OP's requirement:

SELECT DISTINCT(TC.table_name) AS TableName, KC.column_name as ColumnName,
TC. CONSTRAINT_TYPE AS ConstraintType
FROM 
information_schema.table_constraints TC,  
information_schema.key_column_usage KC  
WHERE
TC.CONSTRAINT_TYPE IN ('FOREIGN KEY', 'PRIMARY KEY') 
and KC.table_name = TC.table_name and KC.TABLE_SCHEMA = TC.TABLE_SCHEMA
and KC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
and KC.table_schema = DATABASE();

Related Solutions

Mysql – What would be the fastest approach to bulk update this thesql table (Innodb)

The first thing you need to determine about col2 is if it can be a PRIMARY KEY.

Run this query

SELECT COUNT(1),col2 FROM table GROUP BY col2 HAVING COUNT(1) > 1;

If nothing comes back, then col2 can be a UNIQUE KEY. If even one row comes back, then col2 cannot be a UNIQUE KEY. You can create an index on it.

Since this query would take a while without an index, just go ahead and index it

ALTER TABLE table ADD INDEX col2 (col2);

but that might take a very long time.

You may need a temp table to accomplish this:

Step 01) Create a table that will hold the one million col2 values

DROP TABLE IF EXISTS col2values;
CREATE TABLE col2values ENGINE=InnoDB SELECT col2 FROM table WHERE 1=2;
ALTER TABLE col2value ADD PRIMARY KEY (col2);

Step 02) You can load the col2values table with the one million col2 values

Step 03) Index col2

CREATE TABLE table2 LIKE table;
ALTER TABLE table2 ADD INDEX col2 (col2);
INSERT INTO table2 SELECT * FROM table;
DROP TABLE table;
ALTER TABLE table2 RENAME table;

Step 04) Perform update using INNER JOIN

UPDATE col2values A
INNER JOIN table B
USING (col2)
SET B.col1=1;

In the above steps

Step 03 needs to be done only once
Step 03 is done with 5 lines rather than ALTER TABLE table ADD INDEX col2 (col2); because it would require a full rollback operation should anything go wrong.
All other steps are your bulk insert steps going forward

Give it a Try !!!

Mysql – Best approach to design a database model for this situation? Clients and records

You should organize the data according to its cardinality.

If you have one header and one footer per client, then these columns can be part of your client table. Since there are clearly multiple body records per client, these should be kept in another table.

If you can have multiple records per client, then your "with hierarchy" model is not bad, although it could be simplified to collapse the Header and Footer into Record since there is only one header and one footer per record.

Your "without hierarchy" model is less desirable. If you have multiple records per client it won't tell you which headers/bodies/footers go together. If you have only one record per client, then having separate tables for header and footer is overkill for the same reason as I noted above for the "with hierarchy" model.

This is what I would recommend, depending on how many records each customer can have:

ERD

Best Answer

Related Solutions

Mysql – What would be the fastest approach to bulk update this thesql table (Innodb)

Mysql – Best approach to design a database model for this situation? Clients and records

Related Question