Hi,
Your primary key is EnquireID while you partition on
enquire_date. All fields of the partition function
must be part of a primary key/unique index if they
exist. The problem is that we don't support any other
way of ensuring uniqueness constraint.
So I see 2 solutions, either turn primary key into
an index or have a primary key on the composite key
(EnquireID, enquire_date)
Rgrds Mikael
nickels wrote:
> I am having difficulty understanding this error
> message (1482):
> "A PRIMARY KEY need 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 times. Last edit at 05/12/06 04:08AM by
> nickels.
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com