Re: MySQL Foreign Key Issue

Luchino - Samel wrote:

*Question No. 01* there are some kind of foreign key in database Some of them want give any error when you delete from the table where the foreign key is in, this cause the remove is "recursive" and it delete also the ORDER linked to the CUSTOMER (CASCADE foreign key). In other kind of foreign key (I don't remember the name) you cannot delete CUSTOMER if there is some ORDER linked to them. restrict

hope this will help c-ya 2007/3/26, Lasitha Alawatta <Lasitha.A@dotw.com>:

Hello, I have 2 issue, regarding MySQL "Foreign Key". I have two tables; Table 01 *CUSTOMER* column name characteristic SID Primary Key Full_Name Table *ORDERS* column name characteristic Order_ID Primary Key Order_Date Customer_SID Foreign Key Amount When I run "ALTER TABLE ORDERS ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);" that sql statement, I inserted 2 records to both tables. * * *Question No. 01.* Then I removed 1 record from CUSTOMER table. But It want give any error message. It should give an error message, because in ORDERS table already have some records relevant to the deleted customer record in CUSTOMER table. you have restrict constraint on Customer_SID in table orders. You have 2 options:

1. delete from orders where Customer_SID=foo

       delete from customer where SID=foo
   2. read http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html