Adam Bartosik wrote:
>> MySQL 4.x and 5.0 will accept the above SQL syntax, but will silently
>> ignore it, and not actually perform any referential integrity checking,
>> nor cascading. ie. It all looks like it's working fine, until it breaks
>> horribly when you get into detailed testing. You have been warned.
>
> Please, please RTFM before writing such things, eg:
> http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html
> It is better to know how stuff works - there are different formats of
> tables (storages) in MySQL so you can use what is better for your
> needs and defaults are not always the best. It just like flexibility
> in Catalyst compared to ... Rails?
Attached are two SQL files. One of them uses "default" MySQL tables, the
other one creates them as InnoDB tables.
I have run them against MySQL 5 and in both cases, the foreign key
constraints and cascading deletes are silently ignored. How is that
"better for your needs"?
You're welcome to verify this yourself. I include the captured output
too, although it'll make more sense if you look at the SQL first.
I also include the output from running the normal SQL through Postgres
8, and you can see the difference - the cascading delete occurs, and an
error is thrown when an invalid foreign key is inserted.
Note: I am aware that there are workarounds available to make MySQL
overcome these limitations - however, I stand by the belief that it is
harmful that MySQL silently ignores the standard SQL syntax.
-Toby
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
username VARCHAR(32)
);
CREATE TABLE roles (
role VARCHAR(32) PRIMARY KEY
);
CREATE TABLE accounts_to_roles (
account INTEGER NOT NULL REFERENCES accounts(id)
ON DELETE CASCADE,
role VARCHAR(32) NOT NULL REFERENCES roles(role)
ON DELETE CASCADE,
PRIMARY KEY(account,role)
);
BEGIN;
INSERT INTO accounts(id, username) VALUES (1, 'Toby');
INSERT INTO accounts(id, username) VALUES (2, 'Bob');
INSERT INTO roles(role) VALUES ('Admin');
INSERT INTO roles(role) VALUES ('Luser');
COMMIT;
BEGIN;
-- Test cascading delete:
INSERT INTO accounts_to_roles (account, role) VALUES (2, 'Luser');
DELETE FROM accounts WHERE id=2;
SELECT * FROM accounts_to_roles;
COMMIT;
BEGIN;
-- Test foreign key constraint:
INSERT INTO accounts_to_roles(account, role) VALUES (1, 'Admin');
INSERT INTO accounts_to_roles(account, role) VALUES (1, 'Fake');
INSERT INTO accounts_to_roles(account, role) VALUES (3, 'Unreal');
SELECT * FROM accounts_to_roles;
COMMIT;
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
username VARCHAR(32)
) ENGINE=InnoDB;
CREATE TABLE roles (
role VARCHAR(32) PRIMARY KEY
) ENGINE=InnoDB;
CREATE TABLE accounts_to_roles (
account INTEGER NOT NULL REFERENCES accounts(id)
ON DELETE CASCADE,
role VARCHAR(32) NOT NULL REFERENCES roles(role)
ON DELETE CASCADE,
PRIMARY KEY(account,role)
) ENGINE=InnoDB;
BEGIN;
INSERT INTO accounts(id, username) VALUES (1, 'Toby');
INSERT INTO accounts(id, username) VALUES (2, 'Bob');
INSERT INTO roles(role) VALUES ('Admin');
INSERT INTO roles(role) VALUES ('Luser');
COMMIT;
BEGIN;
-- Test cascading delete:
INSERT INTO accounts_to_roles (account, role) VALUES (2, 'Luser');
DELETE FROM accounts WHERE id=2;
SELECT * FROM accounts_to_roles;
COMMIT;
BEGIN;
-- Test foreign key constraint:
INSERT INTO accounts_to_roles(account, role) VALUES (1, 'Admin');
INSERT INTO accounts_to_roles(account, role) VALUES (1, 'Fake');
INSERT INTO accounts_to_roles(account, role) VALUES (3, 'Unreal');
SELECT * FROM accounts_to_roles;
COMMIT;
mysql> \. test.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+---------+-------+
| account | role |
+---------+-------+
| 2 | Luser |
+---------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+---------+--------+
| account | role |
+---------+--------+
| 1 | Admin |
| 1 | Fake |
| 2 | Luser |
| 3 | Unreal |
+---------+--------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> \. test-innodb.sql
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+---------+-------+
| account | role |
+---------+-------+
| 2 | Luser |
+---------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
+---------+--------+
| account | role |
+---------+--------+
| 1 | Admin |
| 1 | Fake |
| 2 | Luser |
| 3 | Unreal |
+---------+--------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "accounts_pkey"
for table "accounts"
CREATE TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roles_pkey" for
table "roles"
CREATE TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"accounts_to_roles_pkey" for table "accounts_to_roles"
CREATE TABLE
BEGIN
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT
BEGIN
INSERT 0 1
DELETE 1
account | role
---------+------
(0 rows)
COMMIT
BEGIN
INSERT 0 1
ERROR: insert or update on table "accounts_to_roles" violates foreign key
constraint "accounts_to_roles_role_fkey"
DETAIL: Key (role)=(Fake) is not present in table "roles".
ERROR: current transaction is aborted, commands ignored until end of
transaction block
ERROR: current transaction is aborted, commands ignored until end of
transaction block
ROLLBACK
_______________________________________________
List: [email protected]
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/[email protected]/
Dev site: http://dev.catalyst.perl.org/
