Does mysqlcheck checks for foreign keys constrain

user_12361700 0
Viewed 40

I was reading the documentation of mysqlcheck here, but i can't undestand what does --check, i mean, I can't understand what error does it checks (I can only find Check the tables for errors. This is the default operation.).

(My goal is to check for foreign key constrain since I've inserteda bunch of records using SET FOREIGN_KEY_CHECKS = 0;)

foreign-keys mysql
1 Answers

I've done this test:

DROP TABLE test2;
DROP TABLE test1;

CREATE TABLE test1(id INT primary key)  ENGINE INNODB;
CREATE TABLE test2(
    id INT PRIMARY KEY,
    CONSTRAINT fk_test1 FOREIGN KEY (id) REFERENCES test1 (id)
) ENGINE INNODB;

INSERT INTO test1 VALUES (1);

SET FOREIGN_KEY_CHECKS = 0;
SELECT @@FOREIGN_KEY_CHECKS;

INSERT INTO test2 VALUES (1);
INSERT INTO test2 VALUES (2);
INSERT INTO test2 VALUES (3);

SET FOREIGN_KEY_CHECKS = 1;
SELECT @@FOREIGN_KEY_CHECKS;

-- INSERT INTO test2 VALUES (4);

SELECT * FROM test1;
SELECT * FROM test2;

Outputs:

0

1

id
1

id
1
2
3

Then I've run mysqlcheck:

>mysqlcheck -c -u root testdb test2
testdb.test2                                     OK

So I think this Check the tables for errors. means errors in data storage (low level), not data consistency

Running CHECK TABLE test2 expectedly gives the same result.

I couldn't find an easy way to chekc foreign keys after turning this check on. It seems the only thing to do is to use with information_schema writing selects for all FK constraints.

user_17679120
Related