I'll reformat the SQL with line breaks so it looks more readable to me.
On Tue, 11 Mar 2008, smriti Sebastian <[EMAIL PROTECTED]> wrote:
I created two tables like this:
create table customer(SID int,
name varchar(20),
primary key (SID));
create table orders(OID int,
O_Date date,
customer_SID int,
primary key (OID),
Foreign key (customer_SID)
references customer (SID)
on delete cascade
on update cascade);
And inserted values into it.but when i deleted a row from customer
which has reference in orders it didn't showed any error..it deleted
the value in customer table while its reference in orders remain
unchanged.
Googling a little ...
<http://forums.mysql.com/read.php?135,172458,185110#msg-185110> says
Unless innodb is the default engine, you need
CREATE TABLE(...) engine=innodb;
The manual at
<http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html>
says
In MySQL Server 3.23.44 and up, the InnoDB storage engine supports
checking of foreign key constraints, including CASCADE, ON DELETE,
and ON UPDATE. See Section 13.2.6.4, "FOREIGN KEY Constraints".
For storage engines other than InnoDB, MySQL Server parses the
FOREIGN KEY syntax in CREATE TABLE statements, but does not use or
store it. In the future, the implementation will be extended to
store this information in the table specification file so that it
may be retrieved by mysqldump and ODBC. At a later stage, foreign
key constraints will be implemented for MyISAM tables as well.
So it may be silently ignoring the foreign key.
The manual at
<http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html>
talks more about it. It doesn't define "parent table" or "child
table", and further Googling makes it look like my first assumption
had it reversed, that the ORDERS table here is the "child table" and
CUSTOMERS is the "parent table", so I gather that deleting from
CUSTOMERS should indeed delete from ORDERS if you use InnoDB.
--
Tim McDaniel, [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
