On Tue, Mar 11, 2008 at 1:51 AM, 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 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
