SEO

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 it's reference in orders remain unchanged.Plz help me
in solving this.

On Tue, Mar 11, 2008 at 1:51 AM, smriti Sebastian
<smriti.sebastuan@gmail.com> 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 it's reference in orders remain unchanged.Plz help me
> in solving this.
>

mysql> DROP TABLE IF EXISTS `orders`,`customer`;
Query OK, 0 rows affected, 1 warning (0.17 sec)

mysql> CREATE TABLE customer(
-> SID int,
-> name varchar( 20 ) ,
-> PRIMARY KEY ( SID )
-> );
Query OK, 0 rows affected (0.27 sec)

mysql> 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
-> );
Query OK, 0 rows affected (0.36 sec)

mysql> INSERT INTO `customer` (`SID` ,`name`)
-> VALUES ('1', 'Rob');
Query OK, 1 row affected (0.17 sec)

mysql> INSERT INTO `orders` (`OID` ,`O_Date` ,`customer_SID`)
-> VALUES ('0', '2008-03-11', '1');
Query OK, 1 row affected (0.19 sec)

mysql> SELECT * FROM `customer`
-> INNER JOIN `orders` ON `customer_SID` =SID;
+-----+------+-----+------------+--------------+
| SID | name | OID | O_Date | customer_SID |
+-----+------+-----+------------+--------------+
| 1 | Rob | 0 | 2008-03-11 | 1 |
+-----+------+-----+------------+--------------+
1 row in set (0.33 sec)

mysql> DELETE FROM `customer`
-> WHERE SID = 1;
Query OK, 1 row affected (0.16 sec)

mysql> SELECT * FROM `orders`;
Empty set (0.00 sec)

mysql> SELECT `ENGINE` FROM `information_schema`.`TABLES`
-> WHERE `TABLE_NAME` IN('orders','customer');
+--------+
| ENGINE |
+--------+
| InnoDB |
| InnoDB |
+--------+
2 rows in set (0.45 sec)


mysql> SHOW CREATE TABLE `customer`\G
*************************** 1. row ***************************
Table: customer
Create Table: CREATE TABLE `customer` (
`SID` int(11) NOT NULL default '0',
`name` varchar(20) default NULL,
PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `orders`\G
*************************** 1. row ***************************
Table: orders
Create Table: CREATE TABLE `orders` (
`OID` int(11) NOT NULL default '0',
`O_Date` date default NULL,
`customer_SID` int(11) default NULL,
PRIMARY KEY (`OID`),
KEY `customer_SID` (`customer_SID`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_SID`) REFERENCES `customer`
(`SID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

If you do not specify ENGINE and your default engine (probably MyISAM
) engine does not support FK constraints then MySQL will silently
ignore your constraint.

Also:
do you want any of these columns to be able to be NULL? I would think
you probably want the NOT NULL option for everything involved.
I like to use 'USING' in my queries
mysql> SELECT * FROM `customer`
-> INNER JOIN `orders` USING(`SID`);
but can not because you have different names for the columns. I would
suggest having single naming convention.

--
Rob Wultsch

I'll reformat the SQL with line breaks so it looks more readable to me.

On Tue, 11 Mar 2008, smriti Sebastian <smriti.sebastuan@gmail.com> 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, tmcd@panix.com