PHP SQL Check Referential Integrity for whole DB

Question

I was wondering if there was an option to check the referntial integrity of my database without checking everything manually. I must mention that I'm completely new to SQL and have the task to check the referential integrity.

With the search I found this question: how to find records that violate referential integrity

which was already helpful, but I got quite a lot of tables and I was wondering if there was a faster way than writting hundreds of the querys in the format of the question I found.

Thanks for helping, Yíu

EDIT: I know that most databases check that automatically, but I'm a student and the task says "These scripts should illustrate updating and referential integrity of your database. ! Please point explicitly where you check referential integrity (adding rows, modifying rows, deleting rows). "

So I guess I have to check it manually.

Those checks are done by MySQL automatically when you try to insert data (+ when you do not have disabled checks manually, + when you added referential integrity to your structure before). — Daniel W.
– Daniel W., Commented Jan 8, 2015 at 12:46

user4420255 · Accepted Answer · 2015-01-08 12:42:47Z

The most databases check referencing, like MySQL too. So have a look at FOREIGN KEY and REFERENCES command

http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Sample from MySQL page

CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;

Child automatically check and need parent row. If you delete parents, all child rows will deleted to (ON DELETE CASCADE).

Thanks for your answer. Unfortunately I guess I have to do it manually for practice reasons (see edited question). Would be nice if you could have a second look on it.
@Yíu Never do it manually. It's not intended to be done by hand. It's not safe at all. What you need is the part: FOREIGN KEY (...) REFERENCES (...) ON DELETE ... or ON UPDATE... see the linked docs.

Collectives™ on Stack Overflow

PHP SQL Check Referential Integrity for whole DB

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related