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
