1

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.

Community
  • 1
  • 1
Yíu
  • 383
  • 3
  • 14
  • 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. Jan 08 '15 at 12:46

1 Answers1

1

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 Jan 08 '15 at 12:51
  • With ALTER TABLE you can add table contrains. –  Jan 08 '15 at 12:59
  • @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. – Daniel W. Jan 08 '15 at 13:06