Can't understand this error message
Posted by:
nickels
Date: May 12, 2006 02:04AM
I am having difficulty understanding this error message (1482):
"A PRIMARY KEY need [sic] to include all fields in the partition function".
Could somebody please explain what this means: does it mean that a primary key must
be included - if so, then how?
Here's my show create:
CREATE TABLE enquiries_test (
`Enquiry ID` int(11) NOT NULL AUTO_INCREMENT,
`Master Enquiry ID` int(11) DEFAULT NULL,
`enquiry_date` date NOT NULL DEFAULT '0000-00-00',
`Sale Date` date DEFAULT NULL,
`Sale Status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`Date Last Modified` date DEFAULT NULL,
`Enquiry Source` int(11) DEFAULT NULL,
`Customer ID` int(11) DEFAULT NULL,
`Sales Staff ID` int(11) NOT NULL DEFAULT '0',
`Vehicle Price ID` int(11) DEFAULT NULL,
`GovtContract` varchar(50) NOT NULL DEFAULT '',
`Negotiated Vehicle Price` decimal(21,2) DEFAULT '0.00',
`Current_Orig` tinyint(1) DEFAULT '0',
`Orig_LP_Ex_GST` decimal(21,2) DEFAULT '0.00',
`List Price Incl GST` decimal(21,2) DEFAULT '0.00',
`Delivery Fee Paid` decimal(21,2) DEFAULT '0.00',
`Est_Delivery_Date` date DEFAULT NULL,
`Stamp Duty Paid` decimal(21,2) DEFAULT '0.00',
`Vehicle Colour` int(11) DEFAULT NULL,
`Vehicle Trim` int(11) DEFAULT NULL,
`Rego User Type` int(11) unsigned DEFAULT '1',
`Registration Fee Paid` decimal(21,2) DEFAULT '0.00',
`Rego Time Period` int(11) unsigned DEFAULT '4',
`Rego District` int(11) unsigned DEFAULT '1',
`Rego State` char(3) DEFAULT 'SA',
`CTP_Premium_Paid` decimal(21,2) DEFAULT '0.00',
`CTP_ITC_Entitled` tinyint(1) DEFAULT '0',
`CTP_Type` int(11) unsigned NOT NULL DEFAULT '1',
`Appraisals` tinyint(3) unsigned DEFAULT '0',
`Deposit Paid` decimal(21,2) DEFAULT '0.00',
`Receipt Number` varchar(50) DEFAULT NULL,
`Balance Financed By` varchar(50) DEFAULT NULL,
`Customers Order Number` varchar(50) DEFAULT NULL,
`Demo Date` date DEFAULT '0000-00-00',
`No Sale reason` int(11) unsigned DEFAULT NULL,
`Fleet` tinyint(1) DEFAULT '0',
`Fleet Discount` decimal(21,2) DEFAULT '0.00',
`IgSD` tinyint(1) DEFAULT '0',
`IgReg` tinyint(1) DEFAULT '0',
`IgDelFee` tinyint(1) DEFAULT '0',
`GST Rate` float DEFAULT '0',
`Enquiry Complete` tinyint(1) NOT NULL DEFAULT '0',
`Discount Type` int(11) unsigned DEFAULT '0',
`Status` varchar(10) DEFAULT 'NNNNNNNNNN',
`Next Contact Date` date DEFAULT NULL,
`Notes` text,
`Delivery Date` date DEFAULT NULL,
`Completely Invoiced` tinyint(1) DEFAULT '0',
`Use Accessory Advertised Price` tinyint(1) DEFAULT '0',
`Use Vehicle Advertised Price` tinyint(1) DEFAULT '0',
`Sales Rank` int(4) unsigned NOT NULL DEFAULT '0',
`Letter Sent` tinyint(1) unsigned NOT NULL DEFAULT '0',
`Addendum Notes` text,
`Amendment Date` date DEFAULT NULL,
`Common Expiry Date` date DEFAULT NULL,
`Post_Sale_Letter_Sent` tinyint(3) unsigned DEFAULT NULL,
`GM_Onum` int(11) unsigned DEFAULT '0',
`Rego Category` int(11) unsigned DEFAULT '0',
`Additional_Plate_Type` varchar(100) DEFAULT NULL,
`Additional_Plate_Cost` decimal(21,2) NOT NULL DEFAULT '0.00',
`Type` char(1) DEFAULT NULL,
`SmartPak` varchar(10) DEFAULT NULL,
`TStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Enquiry ID`),
KEY `Customer I ID` (`Customer ID`),
KEY `Master Enquiry ID` (`Master Enquiry ID`),
KEY `SalesStaff ID` (`Sales Staff ID`),
KEY `Vehicle Price ID` (`Vehicle Price ID`),
KEY `Source` (`Enquiry Source`),
KEY `Colour` (`Vehicle Colour`),
KEY `Trim` (`Vehicle Trim`),
KEY `Rego User` (`Rego User Type`),
KEY `Rego Time` (`Rego Time Period`),
KEY `Rego District` (`Rego District`),
KEY `CTP Type` (`CTP_Type`),
KEY `Discount Type` (`Discount Type`),
KEY `NS Reason` (`No Sale reason`),
KEY `GM Onum` (`GM_Onum`),
KEY `Rego Category` (`Rego Category`),
KEY `enq_date` (`enquiry_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (year(enquiry_date)) (
PARTITION p0 VALUES LESS THAN (2000),
PARTITION p1 VALUES LESS THAN (2001),
PARTITION p2 VALUES LESS THAN (2002),
PARTITION p3 VALUES LESS THAN (2003),
PARTITION p4 VALUES LESS THAN (2004),
PARTITION p5 VALUES LESS THAN (2005),
PARTITION p6 VALUES LESS THAN (2006),
PARTITION p7 VALUES LESS THAN MAXVALUE
);
Thanks
nick
(yes, I know this table needs 'vertical partitioning'!
Edited 1 time(s). Last edit at 05/12/2006 02:08AM by nickels.