New to MySql

Code Cleric

Code Cleric

Perch
Hey guys... I am new to MySql. I was playing with the admin utility offered through H-Sphere. It wasn't the easiest thing for me to play with, so I searched around and downloaded the "MySql Control Center 0.9.4-beta"... I like this utility a lot more. My question, finally getting to it, is in creating table relations. I had previously created a database for a restaurant in MS Access. I'll give an example. One table was menu items, another was menu categories. There was a field in the menu items table called category that was related to the menu categories. It was easy in Access since it's all gui, click on relationships and viola. I am REALLY new to mySQL, if anyone can offer some input as to how I can create a relationship between two tables in mySQL, please do. I know that I need to use mySQL for database backends to my sites, as ACCESS will fall to its knees with much traffic. Thanks in advance.
 
In order to use table relations you should use the InnoDB format available in MySQL and not the default MYISAM. The syntax is located here:

http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html

For example:

Code: 
DROP TABLE IF EXISTS `tblcountries`;
CREATE TABLE `tblcountries` (
  `CtryID` char(3) NOT NULL default '',
  `CtryName` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`CtryID`)
) TYPE=InnoDB;


DROP TABLE IF EXISTS `tblcompany`;
CREATE TABLE `tblcompany` (
  `CpnyID` int(11) NOT NULL auto_increment,
  `CpnyName` varchar(50) NOT NULL default '',
  `CpnyContactFirstName` varchar(30) default NULL,
  `CpnyContactLastName` varchar(30) default NULL,
  `CpnyAddress1` varchar(50) NOT NULL default '',
  

  .....
  
  
  `CtryID` char(3) NOT NULL default '',
  PRIMARY KEY  (`CpnyID`),
  KEY `idxFK_CtryID` (`CtryID`),
  CONSTRAINT  FOREIGN KEY (`CtryID`) REFERENCES `tblcountries` (`CtryID`) 
ON UPDATE CASCADE
ON DELETE RESTRICT
) TYPE=InnoDB;

Pay special attention to the last 6 lines. They are the most important. I always create my relations by hand so I can recreate them using phpMyAdmin (and excellent tool in my opinion). Try to create all your InnoDB tables with names in lowercase because the Windows implementation does not manage mixed case like "MyTable" (unlike the Linux one) and if you want to test your db on your machine you may have some problems.


P.S. I also think that "MySql Control Center" sucks for managing innodb tables :D
 
Sorry bout the long time no reply..... Newborns are time consuming.... Anywho... I tried

Code: 
CREATE TABLE `tbl_makes` (
  `make_id` int(11) NOT NULL auto_increment,
  `make_name` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`make_id`)
) TYPE=InnoDB;

Just to get started.... It created a MyISAM... Also looked though the myphpadmin and found no way to create Innodb.
 
When you create the table, on the bottom of the page there is a "Table comments" and next to it a "Table type" dropdown list.

If you need to change the table type retroactively, click the tablename on the left, then Options on the top right and you'll be able to change it there.
 
Thank-you subspace for the reply..... In the drop down table type under options InnoDB is not an option. Nor can I seem to create an InnoDB table with MySQL Control Center. Are you able to create an InnoDB table using phpmyadmin? Another thing I noticed about phpmyadmin.... When I first log in... I get an error message, "The additional Features for working with linked Tables have been deactivated." Any thoughts?
 
Code Cleric said:
In the drop down table type under options InnoDB is not an option. Nor can I seem to create an InnoDB table with MySQL Control Center.
Click to expand...

Hmm, I logged onto JodoHost's phpmyadmin page, saw a bunch of table types but apparently forgot to check if InnoDB was actually listed.

MySQL was apparently compiled without InnoDB support :(
BDB seems to be enabled so you can at least use transactions, but I don't believe it has foreign key constraints..

Af for the phpmyadmin error, that's one of the newer phpmyadmin features. I haven't set it up on my own server yet either. It could be practical, but I wouldn't worry about it too much.

Yash, any reason for not supporting InnoDB?
 
Code Cleric said:
Just to get started.... It created a MyISAM... Also looked though the myphpadmin and found no way to create Innodb.
Click to expand...

Hi,

if you specified the InnoDB table type in the SQL sentence, and a MyISAM table was created instead, it is a sign that InnoDB support is not included in the mySQL server of JodoHost. Mabe you can open a ticket requesting this feature

And regarding Control center, I told you that it sucks :D
 
You must log in or register to reply here.
Back
Top