#675 Composite foreign key constraint not shows in table detalisation

[Alhimic]

Dump shows "child_ibfk_1" composite key. In attachement - adminer screenshot/

Version: 10.1.35-MariaDB-1~wheezy

-- Adminer 4.7.1 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS child;
CREATE TABLE child (
fk_field_1 tinyint(2) NOT NULL,
fk_field_2 varchar(20) NOT NULL,
field_3 varchar(100) NOT NULL,
KEY fk_field_1_fk_field_2 (fk_field_1,fk_field_2),
** CONSTRAINT child_ibfk_1 FOREIGN KEY (fk_field_1, fk_field_2) REFERENCES parent (pk_field_1, pk_field_2)**
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 2019-04-16 11:37:39

[Alhimic]

Sorry, wrong screenshot. This actual

[Jacques M]

Same here. A simple table with a FK on 2 columns or more isn't shown in the list. We just can see the index (and we can't delete it of course);
show create table 'my_table' shows the FKs correctly.

Quite boring because to modify the FK, you have to drop / recreate manually

Thank you for your investigations

Jacques

[Thomas G. Jensen]

I have the same problem. Thanks!
Br, Thomas

[Jakub Vrána]

Fixed by 36ade4e1.

[funkjedi]

The change in that commit to rely on information schema has made adminer unusable. The performance of querying the KEY_COLUMN_USAGE table in many cases is just brutal (i.e. mins not ms).

I realize this comment is not particularly helpful. It's more so just to have this out there.

[Jakub Vrána]

This is actually very useful, thanks for the feedback. Can you please benchmark these queries?

-- case 1
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'your_table_here';
-- repeat for every row:
SELECT COLUMN_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = 'CONSTRAINT_NAME_from_previous_query' ORDER BY ORDINAL_POSITION;

-- case 2
SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'your_table_here';
SELECT COLUMN_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME IN ('first_CONSTRAINT_NAME_from_previous_query', 'second_CONSTRAINT_NAME_from_previous_query', ...) ORDER BY CONSTRAINT_NAME, ORDINAL_POSITION;

-- case 3
SELECT *
FROM information_schema.REFERENTIAL_CONSTRAINTS
LEFT JOIN information_schema.KEY_COLUMN_USAGE ON REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME AND REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA = KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA
WHERE REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA = DATABASE() AND REFERENTIAL_CONSTRAINTS.TABLE_NAME = 'your_table_here';

Can you please also replace * in the case 1 and 2 by CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, REFERENCED_TABLE_NAME, DELETE_RULE, UPDATE_RULE to see if it makes any difference?

If all of these queries perform poorly then I'll have go back to parsing the SHOW CREATE TABLE because performance is a development priority for Adminer.

[funkjedi]

Here's the low-down.

-- case 1
Q1 - 0.6s with select *
Q1 - 0.5s with columns specified
-- repeat for every row:
Q2 - 55.1s
Q2 - 55.9s


-- case 2
Q1 - 0.6s with select *
Q1 - 0.5s with columns specified
Q2 - 52.1s


-- case 3
Q1 - 52.9s (also worth noting this query generated hundreds of 1292 'Truncated incorrect DOUBLE value' warnings)

[Jakub Vrána]

I've reverted to the original faster code and fixed a trivial bug that was in that.