Nata Ovchinnikova wrote:
> Hi, everyone.
> I cannot reference a composite PK as FK in child
> table. I try to write a triger to cascade update
? no?
It works for me..
mysql> use test;
Database changed
mysql> create table p(name varchar(64), type varchar(64)) ENGINE = INNODB;
Query OK, 0 rows affected (0.19 sec)
mysql> create table c(name varchar(64), type varchar(64),number int) ENGINE = INNODB;
Query OK, 0 rows affected (0.11 sec)
mysql> alter table p add primary key(name,type);
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table c(name varchar(64), type varchar(64),number int);
Query OK, 0 rows affected (0.11 sec)
mysql> alter table c add foreign key(name,type) references p (name,type) on delete cascade on update cascade;
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into p values ('name1','type1');
Query OK, 1 row affected (0.04 sec)
mysql> insert into p values ('name2','type1');
Query OK, 1 row affected (0.04 sec)
mysql> insert into c values ('name2','type1',1);
Query OK, 1 row affected (0.05 sec)
mysql> insert into c values ('name2','type1',2);
Query OK, 1 row affected (0.04 sec)
mysql> update p set type = 'type2';
Query OK, 2 rows affected (0.30 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from c;
+-------+-------+--------+
| name | type | number |
+-------+-------+--------+
| name2 | type2 | 1 |
| name2 | type2 | 2 |
+-------+-------+--------+
2 rows in set (0.01 sec)
mysql> delete from p ;
Query OK, 2 rows affected (0.07 sec)
mysql> select * from c;
Empty set (0.00 sec)
mysql>
To answer your original question, you cannot do so directly. Alas, mysql lacks the capability to raise (signal) an exception.
There are workarounds though:
http://mysql.gilfster.com/page.php?parent_id=2&page_id=2.0.7
and
http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html