Page 1 of 1

Creating Foreign Key using phpMyAdmin

Posted: Thu Nov 09, 2006 9:54 am
by christian_phpbeginner
Hello,

I usually use SQL syntax when creating a foreign key. Today, I want to learn using phpMyAdmin.

Now, I have two tables for example, namely tbladdress and tbluser.

in tbladdress, there is userID field. And usually, using SQL syntax I would do something like this in the tbladdress while creating the table using SQL:

Code: Select all

CREATE TABLE tbladdress (
   addressID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
   userID INT UNSIGNED NOT NULL REFERENCES tbluser(userid)
);
Refer to the REFERENCES...

Using phpMyAdmin I created the tables. And while creating tbladdress I choose userid INT UNSIGNED NOT NULL using the GUI...but I am not sure that it's referenced to field userid within the tbluser ?? How do I know that it's referenced already ?

Thanks,
Chris

Posted: Thu Nov 09, 2006 12:24 pm
by christian_phpbeginner
Ah, finally got the answer to my question. Better to use SQL syntax rather than using phpMyAdmin on database creations.

I tried to create tbladdress and tbluser using phpMyAdmin, then using this SQL syntax:

Code: Select all

SHOW CREATE TABLE tbladdress;
It gave me output:

Code: Select all

CREATE TABLE IF NOT EXISTS tbluser 
(
   userID INT UNSIGNED NOT NULL AUTO_INCREMENT,
   userTypeID INT UNSIGNED NOT NULL,
   username VARCHAR(16) NOT NULL,
   password VARCHAR(20) NOT NULL,
   cardIDNumber VARCHAR (100) NOT NULL,
   userFirstName VARCHAR (250) NOT NULL,
   userLastName VARCHAR (250) NULL,
   userFullName VARCHAR (250) NOT NULL,
   userBirthPlace VARCHAR(50) NOT NULL,
   userBirthday DATE NOT NULL,
   gender ENUM('Male', 'Female') NOT NULL,
   maritalStatusID INT UNSIGNED NOT NULL,
   motherTongue VARCHAR(255) NOT NULL,
   userPhoto VARCHAR(255) NULL,
   registeredOn DATETIME NOT NULL,
   lastUpdated DATETIME NULL,
) TYPE = InnoDB;

But using SQL prepared statements on database creation, then using the SHOW CREATE TABLE tblname in MySQL function, indeed it corrects things, and output just as I wanted:

Code: Select all

CREATE TABLE IF NOT EXISTS tbluser 
(
   userID INT UNSIGNED NOT NULL AUTO_INCREMENT,
   userTypeID INT UNSIGNED NOT NULL,
   username VARCHAR(16) NOT NULL,
   password VARCHAR(20) NOT NULL,
   cardIDNumber VARCHAR (100) NOT NULL,
   userFirstName VARCHAR (250) NOT NULL,
   userLastName VARCHAR (250) NULL,
   userFullName VARCHAR (250) NOT NULL,
   userBirthPlace VARCHAR(50) NOT NULL,
   userBirthday DATE NOT NULL,
   gender ENUM('Male', 'Female') NOT NULL,
   maritalStatusID INT UNSIGNED NOT NULL,
   motherTongue VARCHAR(255) NOT NULL,
   userPhoto VARCHAR(255) NULL,
   registeredOn DATETIME NOT NULL,
   lastUpdated DATETIME NULL,
   PRIMARY KEY (userID),
   UNIQUE (username),
   FOREIGN KEY (userTypeID) REFERENCES tblusertype (userTypeID),
   FOREIGN KEY (maritalStatusID) REFERENCES tblmaritalstatus (maritalStatusID)
) TYPE = InnoDB;
Well, even though the answer has been found, but I am still interested of how to REFERENCES or creating FOREIGN KEY in phpMyAdmin. Ach, one more thing though. I am using MySQL version 5 above....I guess the keyword ON DELETE CASCADE doesn't work somehow in MySQL 5 ?

Thanks,
Chris

Posted: Fri Nov 10, 2006 3:26 am
by dibyendrah
I'm using MySQL 5.0 and using innoDB table engine. In my database those constrainsts works fine. To be able to use onstraints the foreign key which we define on child table must have same data type as of the field on primary table.

Looking at your SQL script, you should change like follows :

Change

Code: Select all

CREATE TABLE tbladdress (
   addressID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
   userID INT UNSIGNED NOT NULL REFERENCES tbluser(userid)
); 
To

Code: Select all

CREATE TABLE `tbladdress` (                                                                                                  
  `addressID` int(10) unsigned NOT NULL auto_increment,                                                                      
  `userID` int(10) unsigned NOT NULL,                                                                                        
  PRIMARY KEY  (`addressID`),                                                                                                
  KEY `FK_tbladdress` (`userID`),                                                                                                   
   CONSTRAINT `CONSTRAINT_tbladdress` FOREIGN KEY (`userID`) REFERENCES `tbluser` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE  
) ENGINE=InnoDB;
); 
Is should work fine.

Cheers,
Dibyendra

Posted: Sat Nov 11, 2006 12:59 am
by christian_phpbeginner
Hello dibyendrah,

Thanks a lot. It works really fine.

Posted: Mon Nov 13, 2006 6:16 am
by dibyendrah
Sometimes using gui tools will be very handy and time saving.

Posted: Mon Nov 13, 2006 12:49 pm
by christian_phpbeginner
dibyendrah wrote:Sometimes using gui tools will be very handy and time saving.
Yes, infact I wrote down the SQL in phpMyAdmin...

Thanks,
Chris

Posted: Mon Nov 13, 2006 11:39 pm
by dibyendrah
SQLYog is free MySQL gui tools which is really good & has lots of features too . Hope you like it.