Hi all,
Sorry, I forgot to tell, that my default table type is InnoDB.
I am trying to run this script below on a MySQL-4.0.11-NT server. It stops
at the UserLogs table, but if I swap the last two tables' creation order,
it runs correctly. Can anyone help me with this?
Thanks
Dan
/* Begin */
CREATE TABLE `Branches` (
`Id` int unsigned not null, /* Unique id of the branch */
`Name` varchar(40) not null, /* The name of the branch */
`Address1` varchar(40) not null,
`Address2` varchar(40) not null,
`Address3` varchar(40) not null,
`Address4` varchar(40) not null,
`PostCode` varchar(10) not null,
`Phone` varchar(15),
`Fax` varchar(15),
`Manager` varchar(40) not null, /* The name of the branch manager */
primary key (`Id`),
index Name (`Name`)
) COMMENT = 'Data of branches';
CREATE TABLE `ClientGroups` (
`Id` int unsigned not null, /* Unique id of the client group */
`Name` varchar(40), /* Name of the client group */
primary key (`Id`),
index Name (`Name`)
) COMMENT = 'Data of groups clients and suppliers can belong to';
CREATE TABLE `Clients` (
`Id` int unsigned not null, /* Unique id of the client */
`Name` varchar(40) not null, /* Name of the client */
`Address1` varchar(40) not null,
`Address2` varchar(40) not null,
`Address3` varchar(40) not null,
`Address4` varchar(40) not null,
`PostCode` varchar(10) not null,
`Email` varchar(60) not null,
`Phone` varchar(15),
`Fax` varchar(15),
`InitDate` datetime,
`CreditLimit` double,
`InsCreditLimit` double,
`Status` int not null,
`RegNumber` varchar(10),
`SalesContactName` varchar(40),
`SalesEmail` varchar(60),
`SalesMobile` varchar(15),
`AccountsContactName` varchar(40),
`AccountsEmail` varchar(60),
`AccountsMobile` varchar(15),
primary key (`Id`),
index Name (`Name`)
) COMMENT = 'Data of customers and suppliers';
CREATE TABLE `Currency` (
`CurrencyId` varchar(3) not null, /* Common id of the currency */
`Short` varchar(3), /* Short sign of the currency */
`Format` int not null, /* Display format of the currency */
`NegFormat` int not null, /* Display format of negative currency */
`UseShort` enum('F', 'T') not null default 'F', /* Use the short sign of
currency is available */
primary key (`CurrencyId`)
) COMMENT = 'Data of currencies';
CREATE TABLE `ProductGroups` (
`Id` int unsigned not null, /* Unique id of the article group */
`Name` varchar(40) not null, /* Name of the article group */
`LoyaltyPercent` double, /* Percentage of loyalty points for the
products that belongs to this group */
`Memo` mediumtext,
`ValueFactor` double,
primary key (`Id`),
index Name (`Name`)
) COMMENT = 'Data of product groups';
CREATE TABLE `Products` (
`Id` varchar(15) not null, /* The unique id of the product */
`Description` varchar(50) not null, /* The description (name) of the
stock */
`ShortDescription` varchar(20) not null, /* The short description of the
stock, used on reports */
`Unit` varchar(5) not null, /* The product unit (piece, metre, etc.) */
`VAT` double not null, /* VAT in percent. (Gross = Net * (100 + VAT) /
100 ) */
`TotalStock` double not null, /* Total stock in all warehouses */
`LoyaltyPercent` double, /* The percentage of loyalty points for this
product */
`BoxQuantity` double,
`ValueFactor` double,
primary key (`Id`),
index Description (`Description`)
) COMMENT = 'Data of the products';
CREATE TABLE `UserGroups` (
`Id` int unsigned not null, /* The unique id of the group */
`Name` varchar(40) not null, /* The name of the group */
`Memo` mediumtext,
primary key (`Id`),
index Name (`Name`)
) COMMENT = 'Data of user groups';
CREATE TABLE `LoyaltyPoints` (
`ClientId` int unsigned not null,
`CurrId` varchar(3) not null,
`LoyaltyPoints` double not null,
primary key (`ClientId`, `CurrId`),
index CurrId (`CurrId`),
foreign key (`ClientId`) references `Clients` (`Id`) on delete cascade
on update cascade,
foreign key (`CurrId`) references `Currency` (`CurrencyId`) on update
cascade
) COMMENT = 'Data about loyalti points belonging to clients and currency
types';
CREATE TABLE `TransArchive` (
`Id` int unsigned not null auto_increment, /* See table TransActual */
`BranchId` int unsigned not null,
`ProductId` varchar(15) not null,
`TransId` int not null,
`Date` datetime not null,
`Quantity` double not null,
`Description` varchar(40),
primary key (`Id`),
index ProductId (`ProductId`, `Date`, `TransId`),
index BranchId (`BranchId`, `ProductId`, `Date`, `TransId`),
foreign key (`ProductId`) references `Products` (`Id`) on update cascade,
foreign key (`BranchId`) references `Branches` (`Id`) on update cascade
) COMMENT = 'Data of pruduct transactions before the last stock count';
CREATE TABLE `TransActual` (
`Id` int unsigned not null auto_increment, /* The unique id of the row */
`BranchId` int unsigned not null, /* The id of the branch the row
belongs to */
`ProductId` varchar(15) not null, /* The id of the product the row
belongs to */
`TransId` int not null, /* The id of the transaction type (explanation
in the program) */
`Date` datetime not null, /* The date of the transaction */
`Quantity` int not null, /* The quantity of the product that was moved
by the transaction */
`Description` varchar(40), /* Human readable info about the transaction */
primary key (`Id`),
index ProductId (`ProductId`, `Date`, `TransId`),
index BranchId (`BranchId`, `ProductId`, `Date`, `TransId`),
foreign key (`ProductId`) references `Products` (`Id`) on update cascade,
foreign key (`BranchId`) references `Branches` (`Id`) on update cascade
) COMMENT = 'Data of pruduct transactions since the last stock count';
CREATE TABLE `Barcodes` (
`ProductId` varchar(15) not null, /* The id of the product the row
belongs to */
`BarcodeType` int not null, /* The type of the barcode */
`Barcode` varchar(128) not null, /* The barcode of the product */
primary key (`ProductId`, `BarcodeType`),
index Barcode (`Barcode`),
foreign key (`ProductId`) references `Products` (`Id`) on delete cascade
on update cascade
) COMMENT = 'Data about barcodes which belongs to products';
CREATE TABLE `Suppliers` (
`Id` int unsigned not null, /* The uniqe identifier of the row */
`ProductId` varchar(15) not null, /* The id of the product the row
belongs to */
`ClientId` int unsigned not null, /* The id of the client the row
belongs to */
`OrderingId` varchar(30), /* The id of the product at the client above */
primary key (`Id`),
unique index ProductId (`ProductId`, `ClientId`),
index ClientId (`ClientId`),
foreign key (`ProductId`) references `Products` (`Id`) on delete cascade
on update cascade,
foreign key (`ClientId`) references `Clients` (`Id`) on delete cascade
on update cascade
) COMMENT = 'Data about which supplier has a certain product';
CREATE TABLE `GroLoyForGroup` (
`GroupId` int unsigned not null, /* The product group the row belongs to */
`ClientGroupId` int unsigned not null, /* The client group the row
belongs to */
`LoyaltyPercent` double, /* The percentage of loyalty */
primary key (`GroupId`, `ClientGroupId`),
index ClientGroupId (`ClientGroupId`),
foreign key (`GroupId`) references `ProductGroups` (`Id`) on delete
cascade on update cascade,
foreign key (`ClientGroupId`) references `ClientGroups` (`Id`) on delete
cascade on update cascade
) COMMENT = 'Product group loyalty for client groups';
CREATE TABLE `GroLoyForClient` (
`GroupId` int unsigned not null, /* The product group the row belongs to */
`ClientId` int unsigned not null, /* The client the row belongs to */
`LoyaltyPercent` double, /* The percentage of loyalty */
primary key (`GroupId`, `ClientId`),
index ClientId (`ClientId`),
foreign key (`GroupId`) references `ProductGroups` (`Id`) on delete
cascade on update cascade,
foreign key (`ClientId`) references `Clients` (`Id`) on delete cascade
on update cascade
) COMMENT = 'Product group loyalty for clients';
CREATE TABLE `ProLoyForGroup` (
`ProductId` varchar(15) not null, /* The product the row belongs to */
`ClientGroupId` int unsigned not null, /* The client group the row
belongs to */
`LoyaltyPercent` double, /* The percentage of loyalty */
primary key (`ProductId`, `ClientGroupId`),
index ClientGroupId (`ClientGroupId`),
foreign key (`ProductId`) references `Products` (`Id`) on delete cascade
on update cascade,
foreign key (`ClientGroupId`) references `ClientGroups` (`Id`) on delete
cascade on update cascade
) COMMENT = 'Product loyalty points for client groups';
CREATE TABLE `ProLoyForClient` (
`ProductId` varchar(15) not null, /* The product the row belongs to */
`ClientId` int unsigned not null, /* The client the row belongs to */
`LoyaltyPercent` double not null, /* The percentage of loyalty */
primary key (`ProductId`, `ClientId`),
index ClientId (`ClientId`),
foreign key (`ProductId`) references `Products` (`Id`) on delete cascade
on update cascade,
foreign key (`ClientId`) references `Clients` (`Id`) on delete cascade
on update cascade
) COMMENT = 'Product loyalty points for clients';
CREATE TABLE `Stock` (
`BranchId` int unsigned not null, /* The id of the branch the stock
belongs to */
`ProductId` varchar(15) not null, /* The id of the products the stock
belongs to */
`Stock` double not null,
primary key (`BranchId`, `ProductId`),
index ProductId (`ProductId`),
foreign key (`BranchId`) references `Branches` (`Id`) on update cascade,
foreign key (`ProductId`) references `Products` (`Id`) on delete cascade
on update cascade
) COMMENT = 'The stock of each branch and each product';
CREATE TABLE `GroDiscForGroup` (
`GroupId` int unsigned not null, /* The product group the row belongs to */
`ClientGroupId` int unsigned not null, /* The client group the row
belongs to */
`Discount` double not null, /* The discount in percent */
primary key (`GroupId`, `ClientGroupId`),
index ClientGroupId (`ClientGroupId`),
foreign key (`GroupId`) references `ProductGroups` (`Id`) on delete
cascade on update cascade,
foreign key (`ClientGroupId`) references `ClientGroups` (`Id`) on delete
cascade on update cascade
) COMMENT = 'Product group discounts for client groups';
CREATE TABLE `GroDiscForClient` (
`GroupId` int unsigned not null, /* The product group the row belongs to */
`ClientId` int unsigned not null, /* The client the row belongs to */
`Discount` double not null, /* The discount in percent */
primary key (`GroupId`, `ClientId`),
index ClientId (`ClientId`),
foreign key (`GroupId`) references `ProductGroups` (`Id`) on delete
cascade on update cascade,
foreign key (`ClientId`) references `Clients` (`Id`) on delete cascade
on update cascade
) COMMENT = 'The product group discounts for clients';
CREATE TABLE `ProDiscForGroup` (
`ProductId` varchar(15) not null, /* The product the row belongs to */
`ClientGroupId` int unsigned not null, /* The client group the row
belongs to */
`Discount` double not null, /* The discount in percent */
primary key (`ProductId`, `ClientGroupId`),
index ClientGroupId (`ClientGroupId`),
foreign key (`ProductId`) references `Products` (`Id`) on delete cascade
on update cascade,
foreign key (`ClientGroupId`) references `ClientGroups` (`Id`) on delete
cascade on update cascade
) COMMENT = 'The product discounts for client groups';
CREATE TABLE `ProDiscForClient` (
`ProductId` varchar(15) not null, /* The product the row belongs to */
`ClientId` int unsigned not null, /* The client the row belongs to */
`Discount` double not null, /* The discount in percent */
primary key (`ProductId`, `ClientId`),
index ClientId (`ClientId`),
foreign key (`ProductId`) references `Products` (`Id`) on delete cascade
on update cascade,
foreign key (`ClientId`) references `Clients` (`Id`) on delete cascade
on update cascade
) COMMENT = 'The product discounts for clients';
CREATE TABLE `ClientToGroup` (
`Id` int unsigned not null auto_increment, /* Uniqe id of the row
(important ordering key) */
`ClientId` int unsigned not null,
`GroupId` int unsigned not null,
primary key (`Id`),
unique index ClientId (`ClientId`, `GroupId`),
index GroupId (`GroupId`),
foreign key (`ClientId`) references `Clients` (`Id`) on delete cascade
on update cascade,
foreign key (`GroupId`) references `ClientGroups` (`Id`) on delete
cascade on update cascade
) COMMENT = 'Data about which groups clients belong to';
CREATE TABLE `Exchange` (
`FromCurrency` varchar(3) not null, /* 1 unit of this currency equals */
`ToCurrency` varchar(3) not null, /* to "rate" of this currency. */
`ValidFrom` datetime not null, /* This exchange rate is valid from this
date */
`Rate` double not null, /* 123 (FromCurrency) = 123 * Rate (ToCurrency) */
primary key (`FromCurrency`, `ToCurrency`, `ValidFrom`),
index ToCurrency (`ToCurrency`),
foreign key (`FromCurrency`) references `Currency` (`CurrencyId`) on
delete cascade on update cascade,
foreign key (`ToCurrency`) references `Currency` (`CurrencyId`) on
delete cascade on update cascade
) COMMENT = 'Exchange rates of different currencies';
CREATE TABLE `PriceTypes` (
`Id` int unsigned not null, /* Unique id of the price type */
`Name` varchar(40) not null, /* The description of the price type */
`SetCurrId` varchar(3) not null, /* The currency the price is given */
`SellCurrId` varchar(3) not null, /* The currency the price is on the
invoices */
primary key (`Id`),
index SetCurrId (`SetCurrId`),
index SellCurrId (`SellCurrId`),
foreign key (`SetCurrId`) references `Currency` (`CurrencyId`) on update
cascade,
foreign key (`SellCurrId`) references `Currency` (`CurrencyId`) on
update cascade
) COMMENT = 'Data of price types';
CREATE TABLE `ProductToGroup` (
`Id` int unsigned not null auto_increment, /* Unique id of the row
(important ordering key) */
`ProductId` varchar(15) not null,
`ProductGroupId` int unsigned not null,
primary key (`Id`),
unique index ProductId (`ProductId`, `ProductGroupId`),
index ProductGroupId (`ProductGroupId`),
foreign key (`ProductId`) references `Products` (`Id`) on delete cascade
on update cascade,
foreign key (`ProductGroupId`) references `ProductGroups` (`Id`) on
delete cascade on update cascade
) COMMENT = 'Data about which groups products belongs to';
CREATE TABLE `UserGroupRights` (
`UserGroupId` int unsigned not null, /* The group the row belongs to */
`SectionId` int not null, /* The section id within the program the row
belongs to */
`RightId` char(1) not null, /* The right id (see: userrights) */
primary key (`UserGroupId`, `SectionId`, `RightId`),
foreign key (`UserGroupId`) references `UserGroups` (`Id`) on delete
cascade on update cascade
) COMMENT = 'Enabled rights of user groups';
CREATE TABLE `Users` (
`Id` int unsigned not null, /* Unique id of the user */
`UserName` varchar(16) binary not null, /* The user's unique identifier
(same as in MySQL Server) */
`FullName` varchar(40) not null, /* The user's full name */
`GroupId` int unsigned not null, /* The group id of the group which the
user belongs to */
`DefaultBranch` int unsigned not null, /* The branch the user works at */
`Memo` mediumtext,
primary key (`Id`),
unique index UserName (`UserName`),
index FullName (`FullName`),
index GroupId (`GroupId`),
index DefaultBranch (`DefaultBranch`),
foreign key (`GroupId`) references `UserGroups` (`Id`) on update cascade,
foreign key (`DefaultBranch`) references `Branches` (`Id`) on update cascade
) COMMENT = 'Data of the users';
CREATE TABLE `AdviceNoteHeads` (
`AdviceNoteId` varchar(10) binary not null, /* The unique identifier of
the advice note */
`ClientId` int unsigned not null, /* The id of the client the advice
note belongs to */
`Date` datetime not null, /* Date of issuing */
`CurrId` varchar(3) not null, /* The id of the currency */
`Net` double not null, /* Net total of the advice note */
`VAT` double not null, /* VAT total of the advice note */
`UserId` int unsigned not null, /* The id of the user whu made the
advice note */
`BranchId` int unsigned not null, /* The id of tha branch the advice
note was made at */
`PrintCount` int unsigned not null,
`InvoicePrintCount` int unsigned not null,
primary key (`AdviceNoteId`),
index ClientId (`ClientId`),
index CurrId (`CurrId`),
index UserId (`UserId`),
index BranchId (`BranchId`),
foreign key (`ClientId`) references `Clients` (`Id`) on update cascade,
foreign key (`CurrId`) references `Currency` (`CurrencyId`) on update
cascade,
foreign key (`UserId`) references `Users` (`Id`) on update cascade,
foreign key (`BranchId`) references `Branches` (`Id`) on update cascade
) COMMENT = 'Advice notes';
CREATE TABLE `Costs` (
`Id` int unsigned not null auto_increment, /* The uniqe identifier of
the row */
`ParentId` int unsigned not null, /* The id of the parens
supplier-product relationship. */
`ValidFrom` datetime not null, /* The validation date of this price. */
`Cost` double not null, /* The price itself per unit */
`CurrId` varchar(3) not null, /* The currency type of the price */
primary key (`Id`),
index ParentId (`ParentId`, `ValidFrom`),
index CurrId (`CurrId`),
foreign key (`ParentId`) references `Suppliers` (`Id`) on delete cascade
on update cascade,
foreign key (`CurrId`) references `Currency` (`CurrencyId`) on update
cascade
) COMMENT = 'Data about prices belonging to suppliers';
CREATE TABLE `Prices` (
`ProductId` varchar(15) not null, /* The id of the product the price
belongs to */
`PriceCode` int unsigned not null, /* The code of the price type the
price is set in */
`ValidFrom` datetime not null, /* The activation date of the price */
`Price` double not null, /* The price in the set currency type */
`Discountable` enum('F', 'T') not null default 'F', /* If false then
discount cannot be given from this price */
primary key (`ProductId`, `PriceCode`, `ValidFrom`),
index PriceCode (`PriceCode`),
foreign key (`ProductId`) references `Products` (`Id`) on delete cascade
on update cascade,
foreign key (`PriceCode`) references `PriceTypes` (`Id`) on delete
cascade on update cascade
) COMMENT = 'Prices of the products';
CREATE TABLE `UserRights` (
`UserId` int unsigned not null, /* The user the row belongs to */
`SectionId` int not null, /* The numeric id of the section within the
program (Tag property of the menu item) */
`RightId` char(1) not null, /* The right id (w = write, r = read, m =
modify, d = delete) */
primary key (`UserId`, `SectionId`, `RightId`),
foreign key (`UserId`) references `Users` (`Id`) on delete cascade on
update cascade
) COMMENT = 'Enabled rights of users';
CREATE TABLE `UserLogs` (
`Id` int unsigned not null auto_increment, /* Unique identifier of the
row */
`UserId` int unsigned not null, /* User's name who has the row */
`When` datetime, /* The exact server time of the row */
`Subject` varchar(40) not null, /* Short description about the event */
primary key (`Id`),
index UserId (`UserId`, `When`),
foreign key (`UserId`) references `Users` (`Id`) on delete cascade on
update cascade
) COMMENT = 'User activity logs';
CREATE TABLE `AdviceNoteItems` (
`Id` int unsigned not null auto_increment,
`AdviceNoteId` varchar(10) binary not null,
`ProductId` varchar(15) not null,
`VAT` double not null,
`Price` double not null,
`Quantity` double not null,
primary key (`Id`),
index AdviceNoteId (`AdviceNoteId`),
index ProductId (`ProductId`),
foreign key (`ProductId`) references `Products` (`Id`) on update cascade,
foreign key (`AdviceNoteId`) references `AdviceNoteHeads` (`AdviceNoteId`)
) COMMENT = 'Items of advice notes and invoices';
/* End */
| Thread |
|---|
| • Thist script does not run 2 | Daniel Kiss | 6 Mar |
