Jump to content

Archived

This topic is now archived and is closed to further replies.

chickenyd

help with creating table MyISAM vs InnoDB

Recommended Posts

hi i am trying to create a database table for a shopping basket :

CREATE TABLE `customers` (
`customerid` int(10) unsigned NOT NULL auto_increment,
`customerusername` varchar(35) NOT NULL default '',
`customerfname` varchar(35) NOT NULL default '',
`customerlname` varchar(35) default NULL,
`customeraddress1` varchar(60) NOT NULL default '',
`customeraddress2` varchar(60) default NULL,
`customercity` varchar(50) NOT NULL default '',
`country` varchar(50) NOT NULL default '',
`customerpostcode` varchar(30) NOT NULL default '',
`customerphone` varchar(25) default NULL,
`customeremail` varchar(40) NOT NULL default '',
PRIMARY KEY (`customerid`)
) TYPE=InnoDB;



CREATE TABLE `tblcart` (
`ct_id` int(10) unsigned NOT NULL auto_increment,
`pd_id` int(10) unsigned NOT NULL default '0',
`ct_qty` mediumint(8) unsigned NOT NULL default '1',
`ct_session_id` char(32) NOT NULL default '',
`ct_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`ct_id`),
KEY `pd_id` (`pd_id`),
KEY `ct_session_id` (`ct_session_id`)
) TYPE=MyISAM AUTO_INCREMENT=58 ;

my question are:
1. i dont understand this line: TYPE=MyISAM AUTO_INCREMENT=58 ;


2. wots the difference between MyISAM and InnoDB and which 1 is best to use.

Share this post


Link to post
Share on other sites
1 ) AUTO_INCREMENT = 58 sets the value to be given to the next inserted record in its auto_increment field.

2 ) InnoDB tables allow you enforce "referential integrety". This means you can't add a cart record for a customer that doesn't exist and you can't delete a customer if that customer has cart records. This said, here is an option for "cascading deletes" which means that if a customer is deleted, then the related cart records would also be deleted. Also you have "cascading updates" so if the cutomer ID is changed for some reason, related cart records would have their cust ID values changed automatically.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.