Referential Integrety constraint

[pawanez4u]

Hi All,

I am PHP programmer, I am using XAMPP software for programming. In MySQL the concept of referential integrety is not working. This is the following link which explains you my requirement. Pl go thru the following code in the link, it is not working..

http://www.informit.com/articles/art...seqNum=12&rl=1

Thank you.

Pavankumar P

[pbmods]

Heya, Pavankumar.

What do you want your code to do? Give an example.
What is your code doing that you don't want it to do? Give an example.
What is your code *not* doing that it is supposed to? Give an example.

[pawanez4u]

Heya, Pavankumar.

What do you want your code to do? Give an example.
What is your code doing that you don't want it to do? Give an example.
What is your code *not* doing that it is supposed to? Give an example.

Hi,


This is what i am doing in the code.

CREATE TABLE parent
(
  par_id   INT NOT NULL,
  PRIMARY KEY (par_id)
) ENGINE = INNODB;
 
CREATE TABLE child
(
  par_id   INT NOT NULL,
  child_id  INT NOT NULL,
  PRIMARY KEY (par_id, child_id),
  FOREIGN KEY (par_id) REFERENCES parent (par_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = INNODB;
 
 
INSERT INTO parent (par_id) VALUES(1),(2),(3);
INSERT INTO child (par_id,child_id) VALUES(1,1),(1,2);
INSERT INTO child (par_id,child_id) VALUES(2,1),(2,2),(2,3);
INSERT INTO child (par_id,child_id) VALUES(3,1);

SELECT * FROM parent;

+--------+
| par_id |
+--------+
|      1 |
|      2 |
|      3 |
+--------+

SELECT * FROM child;

+--------+----------+
| par_id | child_id |
+--------+----------+
|      1 |        1 |
|      1 |        2 |
|      2 |        1 |
|      2 |        2 |
|      2 |        3 |
|      3 |        1 |
+--------+----------+

2) This is what I am doing and want the result to be.

DELETE FROM parent WHERE par_id = 1;
 
SELECT * FROM parent;

+--------+
| par_id |
+--------+
|      2 |
|      3 |
+--------+

UPDATE parent SET par_id = 100 WHERE par_id =2;
 
SELECT * FROM parent;

+--------+
| par_id |
+--------+
|      3 |
|    100 |
+--------+

SELECT * FROM child;

+--------+----------+
| par_id | child_id |
+--------+----------+
|      3 |        1 |
|    100 |        1 |
|    100 |        2 |
|    100 |        3 |
+--------+----------+

This is not happening with my Databse. Even though i do the same process i am getting, when i select the child table.

SELECT * FROM child;

+--------+----------+
| par_id | child_id|
+--------+----------+
|      1 |        1 |
|      1 |        2 |
|      2 |        1 |
|      2 |        2 |
|      2 |        3 |
|      3 |        1 |
+--------+----------+

Pl give me the right code to get the same result.

[amitpatel66]

Hi,


This is what i am doing in the code.

CREATE TABLE parent
(
par_id INT NOT NULL,
PRIMARY KEY (par_id)
) ENGINE = INNODB;

CREATE TABLE child
(
par_id INT NOT NULL,
child_id INT NOT NULL,
PRIMARY KEY (par_id, child_id),
FOREIGN KEY (par_id) REFERENCES parent (par_id)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE = INNODB;


INSERT INTO parent (par_id) VALUES(1),(2),(3);
INSERT INTO child (par_id,child_id) VALUES(1,1),(1,2);
INSERT INTO child (par_id,child_id) VALUES(2,1),(2,2),(2,3);
INSERT INTO child (par_id,child_id) VALUES(3,1);


SELECT * FROM parent;
+--------+
| par_id |
+--------+
| 1 |
| 2 |
| 3 |
+--------+


SELECT * FROM child;
+--------+----------+
| par_id | child_id |
+--------+----------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
+--------+----------+

2) This is what I am doing and want the result to be.

DELETE FROM parent WHERE par_id = 1;

SELECT * FROM parent;
+--------+
| par_id |
+--------+
| 2 |
| 3 |
+--------+

UPDATE parent SET par_id = 100 WHERE par_id =2;

SELECT * FROM parent;
+--------+
| par_id |
+--------+
| 3 |
| 100 |
+--------+

SELECT * FROM child;
+--------+----------+
| par_id | child_id |
+--------+----------+
| 3 | 1 |
| 100 | 1 |
| 100 | 2 |
| 100 | 3 |
+--------+----------+

This is not happening with my Databse. Even though i do the same process i am getting, when i select the child table.

SELECT * FROM child;
+--------+----------+
| par_id | child_id|
+--------+----------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
+--------+----------+



Pl give me the right code to get the same result.

Are you able to update value 2 to 100 in parent table?
This will not be allowed becuase you have dependent records in child table