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

    According to perror:

    Error code 150: Unknown error 150
    150 = Foreign key constraint is incorrectly formed

    If you do a "SHOW INNODB STATUS" after trying the script you'll see an error similar to the following:

    [quote]

    LATEST FOREIGN KEY ERROR

    040725 11:44:43 Error in foreign key constraint of table test/productinfo:
    There is no index in the table test/productinfo where the columns appear
    as the first columns. Constraint:
    FOREIGN KEY x (custid) REFERENCES customerinfo(custid) ) TYPE=InnoDB
    See http://www.innodb.com/ibman.html for correct foreign key definition.
    [/quote]

    From http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html

    In the referencing table, there must be an index where the
    foreign key columns are listed as the first columns in the same
    order. In the referenced table, there must be an index where the
    referenced columns are listed as the first columns in the same
    order. Index prefixes on foreign key columns are not supported.

    This implies you need to add an index on custid in the productinfo table, where the custid is the first part of the index

    i.e.

    CREATE TABLE productinfo(
.
.
.
PRIMARY KEY ( productid ) ,
UNIQUE KEY productid( productid ) ,
INDEX ( productid, custid ) ,
INDEX ( custid ) , <-----------------------------
FOREIGN KEY ( custid ) REFERENCES customerinfo( custid ) 
) TYPE = InnoDB

    Another couple of things I noticed... custid in productinfo is not the same type as custid in customerinfo (you'll get the same error
    unless you make the types identical) and you have 3 indexes on customerinfo.custid - you only need the one - PRIMARY KEY.
    Athough this isn't critical, it'll speed up your INSERT queries

      Write a Reply...