MySQL Forums
Forum List  »  Partitioning

Re: Can't understand this error message
Posted by: Mikael Ronström
Date: May 12, 2006 02:06PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2833
May 12, 2006 02:04AM
Re: Can't understand this error message
2114
May 12, 2006 02:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.