MySQL Forums
Forum List  »  Triggers

Re: cascading for a composite PK
Posted by: Roland Bouman
Date: January 12, 2006 05:41PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2403
January 12, 2006 02:39PM
Re: cascading for a composite PK
1714
January 12, 2006 05:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.