> > CREATE TABLE `his_msisdn_imei_activ_hist` (
> > `MSISDN` varchar(23) NOT NULL,
> > `ACTIV_IMEI` varchar(20) NOT NULL,
> > `ACTIV_PHONE_TYPE` varchar(100) NOT NULL,
> > `PREV_IMEI` varchar(20) default NULL,
> > `PREV_PHONE_TYPE` varchar(100) default NULL,
> > `ACTIV_TIME` datetime NOT NULL,
> > PRIMARY KEY (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`)
> > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> >
>
> This primary key is a bad idea. A VERY VERY bad idea. For starters, a
> primary key should have ONE field, not THREE. While it is allowed, it's
> not going to help performance at all. Next is that the primary key
Care for a fight over this one? :-)
A primary key should be the primary key. If this is 3 columns, or 1
varchar column, it's all fine.
I agree with your point of the ACTIV_TIME being a bad candidate
for being part of a PK though.
Oh, and having multiple columns in a PK does not mean you cannot
create additional indices as/if required.
All in all, your statement about multiple columns in a PK is a very very
bad statement ;-)
> should be a numeric field. You've got varchars and datetimes! Yuck! If
> you want to enforce a rule such as restricting duplicate values, then
> start by creating yourself a sane primary key ( an unsigned int, for
> example ), and *THEN* put an index ( with your "don't allow duplicates"
> rule ) across your (`MSISDN`,`ACTIV_IMEI`,`ACTIV_TIME`) fields.
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
