Hello,
If I change the value of a reference a ,
for instance by means of updating or inserting values,
I'd expect both updated values and inserted values to
cascade, hence to change b,
where
FOREIGN KEY (b) REFERENCES A(a)
ON UPDATE CASCADE
In this example foreign key and reference
are taken from one and the same table,
that should be possible,
please help me , what is wrong ?
My code woun't cascade anything.
Yours Sincerely
Morten Gulbrandsen
===================
--------------
DROP TABLE IF EXISTS A
--------------
Query OK, 0 rows affected (0.04 sec)
--------------
CREATE TABLE A
(
a CHAR(9) NOT NULL,
b CHAR(9),
c INT NOT NULL DEFAULT 1,
PRIMARY KEY (a),
INDEX (b, c)
)ENGINE = INNODB
--------------
Query OK, 0 rows affected (0.05 sec)
--------------
SHOW WARNINGS
--------------
Empty set (0.00 sec)
--------------
DESCRIBE A
--------------
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | char(9) | | PRI | | |
| b | char(9) | YES | MUL | NULL | |
| c | int(11) | | | 1 | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)
--------------
ALTER TABLE A
ADD FOREIGN KEY (b) REFERENCES A(a)
ON DELETE SET NULL
ON UPDATE CASCADE
--------------
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
SHOW WARNINGS
--------------
Empty set (0.00 sec)
--------------
DESCRIBE A
--------------
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | char(9) | | PRI | | |
| b | char(9) | YES | MUL | NULL | |
| c | int(11) | | | 1 | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
--------------
SELECT * FROM A
--------------
Empty set (0.00 sec)
--------------
INSERT INTO A (a) VALUES ('a0')
--------------
Query OK, 1 row affected (0.05 sec)
--------------
INSERT INTO A (a) VALUES ('a1')
--------------
Query OK, 1 row affected (0.02 sec)
--------------
SELECT * FROM A
--------------
+----+------+---+
| a | b | c |
+----+------+---+
| a0 | NULL | 1 |
| a1 | NULL | 1 |
+----+------+---+
2 rows in set (0.02 sec)
--------------
UPDATE A SET a='updated' WHERE a='a0'
--------------
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--------------
SELECT * FROM A
--------------
+---------+------+---+
| a | b | c |
+---------+------+---+
| a1 | NULL | 1 |
| updated | NULL | 1 |
+---------+------+---+
2 rows in set (0.02 sec)
Bye
