CODEWITHSUNDEEP
mysqlforeign-keysreferential-integritygeneric-foreign-key
Yíu
Yíu

Reputation: 383

PHP SQL Check Referential Integrity for whole DB

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.

Upvotes: 1

Views: 335

Answers (1)

user4420255
user4420255

Reputation:

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).

Upvotes: 1

Related Questions