Link to home
Start Free TrialLog in
Avatar of chrisatwork
chrisatwork

asked on

MySQL syntax query using ON DELETE

Hi, I am very new to MySQL and phpMyAdmin so please bear with me.

Using MySQL server version 5.5.25a and phpMyAdmin 3.5.3 on a win7 pro box with xampp to create a php form.

I am trying to create a table in a database that I have other tables working ok

In phpMyAdmin I have logged into the database as an admin user (full privileges) and have created a table USERTABLE with UserID as a primary key.   I am now trying to use the following SQL query to create a table called LOGTABLE :

 CREATE TABLE LOGTABLE (
LogID INTEGER(20) NOT NULL AUTO INCREMENT,
 UserID VARCHAR(16) NOT NULL REFERENCES USERTABLE (UserID) ON DELETE CASCADE ON UPDATE CASCADE,
LogTime datetime NOT NULL,
 IpAddress VARCHAR(15) NOT NULL,
Event VARCHAR(255) NOT NULL,
EventCode SMALLINT(6) NOT NULL,
 PRIMARY KEY (LogID)
)

(Written this way for clarity only)

I am getting the following error when trying to create the table:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AUTO INCREMENT, UserID VARCHAR(16) NOT NULL REFERENCES USERTABLE (UserID) ON D' at line 1

I can't see what I am doing wrong so can someone check it over for me please.

Thanks

Christopher
Avatar of chrisatwork
chrisatwork

ASKER

Ok a bit of digging and I think it has to do with foreign key constraints, but this is a bit out of my league.  The tables are InnoDB type and I think the REFERENCES is ignored, so how do I create those references please?
ASKER CERTIFIED SOLUTION
Avatar of johanntagle
johanntagle
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok tried this:

CREATE TABLE LOGTABLE (
LogID INTEGER(20) NOT NULL AUTO_INCREMENT,
LogUserID VARCHAR(16) NOT NULL,
LogTime datetime NOT NULL,
IpAddress VARCHAR(15) NOT NULL,
Event VARCHAR(255) NOT NULL,
EventCode SMALLINT(6) NOT NULL,
PRIMARY KEY (LogID),
FOREIGN KEY (LogUserID) REFERENCES USERTABLE (UserID) ON DELETE CASCADE ON UPDATE CASCADE
)

 and got the following error message:

#1005 - Can't create table 'mydatabase.logtable' (errno: 150) (Details...)

The details just took me to a summary of InnoDB and didn't explain much until I did a SHOW ENGINE INNODB STATUS which, when I managed to understand what I was doing said:

Error in foreign key constraint of table mydatabase/logtable:FOREIGN KEY (LogUserID) REFERENCES USERTABLE (UserID) ON DELETE CASCADE ON UPDATE CASCADE):Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition

This is only partially meaningful!  For reference, the USERTABLE was created with the following query and appeared to create ok:

CREATE TABLE USERTABLE (UserID VARCHAR (16) NOT NULL,
Password VARCHAR (16) NOT NULL,
UserName VARCHAR (16) NOT NULL,
FirstName VARCHAR (16) NOT NULL,
LastName VARCHAR (30) NOT NULL,
Email VARCHAR (256) NOT NULL,
Verified TINYINT(4) NOT NULL default 0,
Privilege SMALLINT (4) NOT NULL default 0,
PRIMARY KEY (UserID)
)

So, I can drop the usertable and start again as this is on a localhost xampp server and I want to get it right before going to the online server, or I can do anything you suggest to the existing table.  I did check that the collation was the same , utf8_general_ci but the bit about the index baffled me.

Awaiting your suggestions!

Regards

Christopher
I tried out your commands on my local machine and I was able to replicate your error if USERTABLE is MyISAM and LOGTABLE is InnoDB.  Of course, foreign keys only work when the tables involved ar InnoDB.  So convert USERTABLE to Innodb first:

alter table USERTABLE engine=innodb;

 then you should be good to go.
Thanks I will check that out tomorrow.  I thought I had got everything as innodb but it won't hurt to check.  Will let you know

Christopher
Ok checked it out and tables are all innodb as is the database itself and all are utf8_general_ci. So what do you think.  Is it worth trying to do this on my hosts server rather than localhost?
Right, I deleted the tables and started again and everything worked fine so I can accept your answer and close this with thanks.

Just a PS:  I fully expect to be here again soon!

Christopher