Im having a rough time getting this key done right. I have consulted the mysql site, searched the web, etc.. i think my syntax is correct however im getting the same error. my hosts config settings are:
MYSQL 4.0.20
PHP 4.3.2
phpMyAdmin 2.4.0
i can only change md via phpmyadmin. any help would be greatly appriciated. here is my .sql
#
# Table structure for table `customerinfo`
#
DROP TABLE IF EXISTS customerinfo;
CREATE TABLE customerinfo (
custid int(6) unsigned NOT NULL auto_increment,
firstname varchar(50) NOT NULL default '',
lastname varchar(50) NOT NULL default '',
address varchar(50) NOT NULL default '',
address2 varchar(50) default NULL,
city varchar(50) NOT NULL default '',
state varchar(50) NOT NULL default '',
zip varchar(50) NOT NULL default '',
homephone varchar(15) NOT NULL default '',
workphone varchar(15) default NULL,
otherphone varchar(15) default NULL,
email varchar(50) NOT NULL default '',
PRIMARY KEY (custid),
UNIQUE KEY custid (custid),
KEY custid_2 (custid)
) TYPE=InnoDB;
#
# Dumping data for table `customerinfo`
#
INSERT INTO customerinfo (custid, firstname, lastname, address, address2, city, state, zip, homephone, workphone, otherphone, email) VALUES (724041, 'John', 'Smith', '100 Station St.', 'Suite 100', 'Loyalhanna', 'PA', '15661', '724-555-1111', '724-555-1112', '724-555-1113', 'jsmith@smartparts.com');
INSERT INTO customerinfo (custid, firstname, lastname, address, address2, city, state, zip, homephone, workphone, otherphone, email) VALUES (724042, 'Vicki', 'Karson', '252 W. Main St.', '', 'Monongahela', 'PA', '15063', '724-258-6504', '724-436-3999', '', 'vkarson@smartparts.com');
INSERT INTO customerinfo (custid, firstname, lastname, address, address2, city, state, zip, homephone, workphone, otherphone, email) VALUES (724043, 'Anthony', 'Nelson', '3280 Virginia Beach Blvd.', '', 'Virginia Beach', 'VA', '23452-5724', '757-444-2055', '757-444-2088', '', 'nelson@smartparts.com');
INSERT INTO customerinfo (custid, firstname, lastname, address, address2, city, state, zip, homephone, workphone, otherphone, email) VALUES (724044, 'Thomas', 'Gurber', '320 Linden Ave', 'Apt 1', 'Johnstown', 'PA', '15902', '814-539-5356', '', '', 'tgruber@smartparts.com');
#
# Table structure for table `productinfo`
#
DROP TABLE IF EXISTS productinfo;
CREATE TABLE productinfo (
productid int(6) unsigned NOT NULL auto_increment,
custid int,
purchasedate varchar (10) NOT NULL default '',
purchasefrom varchar (50) NOT NULL default '',
warrantyexp varchar (10) NOT NULL default '',
productname varchar (50) NOT NULL default '',
productserial varchar (10) NOT NULL default '',
PRIMARY KEY (productid),
UNIQUE KEY productid (productid),
INDEX (productid, custid),
FOREIGN KEY (custid) REFERENCES customerinfo(custid)
) TYPE=InnoDB;
#
# Dumping data for table `productinfo`
#
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000001, '724041', '01/01/2004', 'The Big Paintball Place', '01/01/2005', 'Shocker SFT', 'SHO12345');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000002, '724041', '05/01/2004', 'Smart Parts Website', '05/01/2005', 'Freak Kit', 'FRE23456');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000003, '724041', '06/01/2004', 'Action Village', '06/01/2005', 'Inline Maxflow', 'IFLO11111');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000001, '724042', '01/01/2004', 'The Big Paintball Place', '01/01/2005', 'Shocker SFT', 'SHO12345');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000002, '724042', '05/01/2004', 'Smart Parts Website', '05/01/2005', 'Freak Kit', 'FRE23456');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000003, '724042', '06/01/2004', 'Action Village', '06/01/2005', 'Inline Maxflow', 'IFLO11111');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000001, '724043', '01/01/2004', 'The Big Paintball Place', '01/01/2005', 'Shocker SFT', 'SHO12345');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000002, '724043', '05/01/2004', 'Smart Parts Website', '05/01/2005', 'Freak Kit', 'FRE23456');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000003, '724043', '06/01/2004', 'Action Village', '06/01/2005', 'Inline Maxflow', 'IFLO11111');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000001, '724044', '01/01/2004', 'The Big Paintball Place', '01/01/2005', 'Shocker SFT', 'SHO12345');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000002, '724044', '05/01/2004', 'Smart Parts Website', '05/01/2005', 'Freak Kit', 'FRE23456');
INSERT INTO productinfo (productid, custid, purchasedate, purchasedfrom, warrantyexp, productname, productserial) VALUES (000003, '724044', '06/01/2004', 'Action Village', '06/01/2005', 'Inline Maxflow', 'IFLO11111');
here is a snapshot of the error form phpMyAdmin
Error
SQL-query :
CREATE TABLE productinfo(
productid int( 6 ) unsigned NOT NULL auto_increment,
custid int,
purchasedate varchar( 10 ) NOT NULL default '',
purchasefrom varchar( 50 ) NOT NULL default '',
warrantyexp varchar( 10 ) NOT NULL default '',
productname varchar( 50 ) NOT NULL default '',
productserial varchar( 10 ) NOT NULL default '',
PRIMARY KEY ( productid ) ,
UNIQUE KEY productid( productid ) ,
INDEX ( productid, custid ) ,
FOREIGN KEY ( custid ) REFERENCES customerinfo( custid )
) TYPE = InnoDB
MySQL said:
Can't create table './spdata/productinfo.frm' (errno: 150)
Back