Creating Foreign Key using phpMyAdmin

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
christian_phpbeginner
Forum Contributor
Posts: 136
Joined: Sat Jun 03, 2006 2:43 pm
Location: Java

Creating Foreign Key using phpMyAdmin

Post 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
User avatar
christian_phpbeginner
Forum Contributor
Posts: 136
Joined: Sat Jun 03, 2006 2:43 pm
Location: Java

Post 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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
User avatar
christian_phpbeginner
Forum Contributor
Posts: 136
Joined: Sat Jun 03, 2006 2:43 pm
Location: Java

Post by christian_phpbeginner »

Hello dibyendrah,

Thanks a lot. It works really fine.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

Sometimes using gui tools will be very handy and time saving.
User avatar
christian_phpbeginner
Forum Contributor
Posts: 136
Joined: Sat Jun 03, 2006 2:43 pm
Location: Java

Post 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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

SQLYog is free MySQL gui tools which is really good & has lots of features too . Hope you like it.
Post Reply