List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:June 27 2006 12:47pm
Subject:Re: CREATE TABLE Failure
View as plain text  
Asif, I note 3 problems:

1 - your table is named 'order', which is a reserved word in MySQL and 
most other db engines.  I'd suggest using a different name.  If you must 
use 'order' for the name, enclose it in backticks, a la `order`, in the 
create statement.

2 - you have IDNEX instead of INDEX on the WaiterID column.

3 - you reference a constraint on the PersonID column, which is not a 
column in this table.

Good luck.

Dan


Asif Lodhi wrote:
> Hi All,
> 
> I have been repeatedly trying to create the following table - without
> __ANY__ success.  Looks like there is REALLY something wrong with the
> MySQL engine or something!  Here is the script:
> 
> CREATE TABLE Order (
>     DID        int         not null,
>     DeskNo        int         not null,
>     OrderDate    datetime    not null,    /* KOT Date */
>     OrderNo        int         not null,    /* KOT No */
>     CRMID        int         not null,    /* Customer Relationship 
> Management ID B2PID */
>     CustomerID    int         not null,    /* PersonID of Customer, 
> Member, etc. */
>     TableNo        int         not null,
>     WaiterID    int         not null,    /* PersonID of Waiter */
>     OrderClerkID    int         not null,    /* PersonID of Order-Clerk */
>     Status        int        not null,    /* Order Status - 
> Open;1;In-Process;2;
>                                         Cooked/Ready;3;Delivered;4;
>                                         Cancelled;-1, -2, -3, ..  */
>             CONSTRAINT Order_UQ UNIQUE INDEX (DID, DeskNo, OrderDate, 
> OrderNo),
>             INDEX CRMID_NDX (CRMID),
>                 FOREIGN KEY CRM_FK_NDX (CRMID) REFERENCES RelationB2P (ID),
>             INDEX CustomerID_NDX (CustomerID),
>                 FOREIGN KEY Customer_FK_NDX (PersonID) REFERENCES Person 
> (PersonID),
>             INDEX DID_NDX (DID),
>                 FOREIGN KEY Department_FK_NDX (DID) REFERENCES 
> EntityBranchDept
> (DepartmentID),
>             IDNEX WaiterID_NDX (WaiterID),
>                 FOREIGN KEY WaiterID_FK_NDX (WaiterID) REFERENCES Person 
> (PersonID),
>             INDEX OrderClerkID (OrderClerkID),
>                 FOREIGN KEY OrderClerk_FK_NDX (OrderClerkID) REFERENCES 
> Person (PersonID)
> ) ENGINE=InnoDB;
> 
> 
> 
> 
> 
> You guys can delete all the index/references and other clauses!  Even
> then this table doesn't get created!
> 
> I'd appreciate your help.
> 
> -- 
> Asif
> 

-- 
Dan Buettner
Thread
CREATE TABLE FailureAsif Lodhi27 Jun
  • Re: CREATE TABLE FailureDan Buettner27 Jun
  • Re: CREATE TABLE FailureMartijn Tonies27 Jun