foreign key

[santhanalakshmi]

hi,
can i anybody tell me..the use of FOREIGN KEY.I am creating two tables,one with the PRIMARY KEY and the other one is the FOREIGN KEY.


For Example :

create table customer(cid int not null,cname varchar(50),PRIMARY KEY(cid))type=innodb;

create table orders(orderid int not null PRIMARY KEY,cost int not null,cid int not null,index(cid),FOREIGN KEY(cid) REFERENCES PRIMARY KEY(cid))type=innodb;

what will do the FOREIGN KEY ?when i am trying to insert a record in the FOREIGN KEY table(ORDERS TABLE),its INSERTING .But its not reflected in the CUSTOMER TABLE.

Same thing,when i am trying to insert a record in the CUSTOMER TABLE,its INSERTING.But its not reflected in the ORDERS TABLE.

Same like for DELETING and for UPDATING.......please help me out.The need of FOREIGN KEY.I cant get it properly.thanks

[dgreenhouse]

Maybe this will help:

CREATE TABLE `customers` (
  `cid` int(10) unsigned NOT NULL auto_increment,
  `cname` varchar(50) default NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=InnoDB;
 
 
CREATE TABLE  `orders` (
  `orderid` int(10) unsigned NOT NULL auto_increment,
  `cid` int(10) unsigned NOT NULL,
  `cost` decimal(10,2) NOT NULL,
  PRIMARY KEY  (`orderid`),
  KEY `cid` (`cid`),
  CONSTRAINT `FK_customers_1` FOREIGN KEY (`cid`) REFERENCES `customers` (`cid`) ON UPDATE CASCADE
) ENGINE=InnoDB;
 

[santhanalakshmi]

hi,
No....its not working.I did not seen any changes in the foreign key table......

[dgreenhouse]

1- When you enter a record into the customers table, there is no reflection in the orders table.

2- When you enter a record into the orders table, you MUST provide a valid customer id (cid) with the other order column values. If you do not, you will get a "foreign key constraint" error.

3- In other words, you MUST insert a valid customer record BEFORE you can insert a valid orders record and a valid orders record is DIRECTLY dependent on a VALID customer record being existent.

I suggest you read up on "referential integrity."
http://en.wikipedia.org/wiki/Referential_integrity
http://databases.about.com/cs/admini...fintegrity.htm
http://www.odbms.org/download/007.02...ber%202005.PDF

4- When a table is setup as related to another, you typically JOIN the two related tables on the related column(s).

e.g.

INSERT INTO customers (cname) VALUES ("Dave Matthews")
INSERT INTO customers (cname) VALUES ("Jack Johnson")
 
INSERT INTO orders (cid,cost) VALUES (1,100.02)
INSERT INTO orders (cid,cost) VALUES (2,5000)
INSERT INTO orders (cid,cost) VALUES (1,323.44)
 
SELECT b.cname,a.orderid,a.cost FROM orders a 
JOIN customers b ON b.cid = a.cid 
ORDER BY b.cname,a.orderid
 
// Output
cname          orderid       cost
==================================
Dave Matthews     1         100.02
Dave Matthews     3         323.44
Jack Johnson      2        5000.00
 

[santhanalakshmi]

Thanks....

before that i should enable INNODB Storage Engine.

mysql>show engines;

showing INNODB -> DISABLED.

Problems is due to this ah.How i should enable INNODB Storage Engine.

mysql version : 5.0.51a


and also i am using XAMPP Version 1.6.6a.

[dgreenhouse]

See:
http://www.mydigitallife.info/2006/1...-installation/

[santhanalakshmi]

Hi,

thanks.But such configuration files(my.cnf or my.ini) are not found in the mysql/bin directory.I am running Xampp in windows XP.Please help me.I want to work out on the FOREIGN KEY.

[dgreenhouse]

The configuration file will probably be 1 directory above the bin directory.

If it is installed in c:\Program Files\MySQL\MySQL Server 5.0\ then that's where you'll more than likely find the file my.ini.