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 */
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
