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:
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