5

I have a primary key in my mysql table which is comprised of three columns. 

CREATE TABLE IF NOT EXISTS `bb_bulletin` (
  `OfficeCode` int(5) NOT NULL,
  `IssuerId` int(11) NOT NULL,
  `BulletinDtm` datetime NOT NULL,
  `CategoryCode` varchar(4) NOT NULL,
  `Title` varchar(255) NOT NULL,
  `Content` text NOT NULL,
  PRIMARY KEY (`OfficeCode`,`IssuerId`,`BulletinDtm`),
  UNIQUE KEY `U_IssuerId` (`IssuerId`,`OfficeCode`,`BulletinDtm`),
  UNIQUE KEY `U_CategoryCode` (`CategoryCode`,`OfficeCode`,`IssuerId`,`BulletinDtm`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Is there a shorthand method to select a record for a given value of the primary key.

I tried. 

SELECT * FROM `bb_bulletin` WHERE PRIMARY = '20001-1-2011-01-07 14:04:40'

Instead of the long hand method of doing, 

SELECT * From bb_bulletin WHERE OfficeCode = 20001 AND IssuerId = 1 AND BulletinDtm = 2011-01-07 14:04:40

What is the standard when dealing php and composite keys in your table. Note: I don't want to add autoincrementing keys to my tables in order to solve this. If it is not possible then I will just pass the three constraints in my url.

andrew
  • 5,096
  • 10
  • 43
  • 57

4 Answers4

8

I see two parts to your question. The first part is about referencing a composite value. I'm not sure whether MySQL support this, but it would be the SQL standard way of doing this:

SELECT * FROM bb_bulletin WHERE (OfficeCode, IssuerId, BulletinDtm) = (20001, 1, '2011-01-07 14:04:40');

The other part is referring to the primary key columns using an abbreviated syntax. I don't know of any such possibility.

Peter Eisentraut
  • 35,221
  • 12
  • 85
  • 90
  • 1
    MySQL does support such syntax (at least in 5.2) – Mchl Jan 25 '11 at 00:14
  • I was just making up the syntax for how I thought it would be done but what you have provided above looks like a good shorthand method if it works. Thanks ill give it a go. – andrew Jan 25 '11 at 00:40
  • Is there a name for what you have done here? Ie specifying the columns as a comma separated list and then the values as a comma separated list? – andrew Jan 25 '11 at 00:41
  • Is this going to cause a problem as the answer below suggested? – andrew Jan 25 '11 at 03:29
  • *Some* Non-SQLs might support that, to ease the php issue that OP is seeking help for, but it is definitely not Standard SQL. – PerformanceDBA Jan 25 '11 at 06:43
  • @andrew: Not sure which answer suggested that this query can cause any problems. There is noting wrong with it, and if you think it suits you, you should go with it (much better than my examples of clumsy 'workarounds') – Mchl Jan 25 '11 at 07:46
  • @andrew: They are called row value constructors. – Peter Eisentraut Jan 25 '11 at 08:56
  • @andrew: just make sure you protect your query from SQL injection (i.e. do not put the string from `$_GET` array directly into the query, but at least `mysql_real_escape_string()` it. – Mchl Jan 25 '11 at 09:00
1

No, there is not such a way, either in MySQL, or in any SQL dialect I know.

You should split 20001-1-2011-01-07 14:04:40 string in your PHP and use its parts to build your MySQL query.

I might also add that, composite primary key might not be the best idea performance wise (especially with InnoDB tables)

Also INT(5) still takes just as much space as INT(11) (or plain INT for that matter). For smaller integer types use TINYINT, SMALLINT and MEDIUMINT

Clumsy Workarounds section

Solutions below should work the way you want, but at the cost of resources and/or performance. Unless you really can't go with the simplest solution, you should not use these.

A horrible way to do it would be like this WHERE CONCAT(OfficeCode,IssuerId,BulletinDtm) = '20001-1-2011-01-07 14:04:40'

It is HORRIBLE because it will not allow MySQL to use the index to actually speed up the query.

Don't do this, please.

Another way. Add a CHAR(32) column to your table and make it your PK. Store in it a MD5 hash of your previous PK columns (i.e. MD5('20001-1-2011-01-07 14:04:40'). Then you can query like: WHERE newPKcolumn = MD5('20001-1-2011-01-07 14:04:40'). This will allow you to do what you want, and MySQL to use the index. The table is no longer normalised, but denormalisation is a tradeoff you need to do sometimes to improve performance or usability. There's nothing wrong with that.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Mchl
  • 61,444
  • 9
  • 118
  • 120
  • Hmm it makes composite keys a bit of a pain in the arse as far as the application is concerned because when you want to target a particular element you need to have a link like. index.php?action=someaction&OfficeCode=x&IssuerId=y&BulletinDtm=z – andrew Jan 24 '11 at 23:42
  • There's not really much else to say besides "nope, not really". I will say that I can't think of any good reason why someone would want to do so, either 8-| ALSO: personally, I stick to single-column autoincrement primary keys all the time, for simplicity (using unique keys for other values, as the op is already doing) – TehShrike Jan 24 '11 at 23:43
  • 1
    Or you need to have a piece of code, that will split a value of PK given in URL into it's composite parts. – Mchl Jan 24 '11 at 23:44
  • Quite hard/impossible to normalize your database if you don't use composite primary keys. – andrew Jan 24 '11 at 23:45
  • There must be a standard way to work with composite keys in mysql with php applications. – andrew Jan 24 '11 at 23:46
  • There is a standard way: action=someaction&OfficeCode=x&IssuerID=y... and when you are done parsing and checking for invalid data, you can then do your select ... where OfficeCode=x and IssuerId=y.... – Wavel Jan 24 '11 at 23:52
  • I tend to agree with @TehShrike and @Mchl; nothing wrong with using a surrogate key for selecting unique records (especially if the key will be passed through a URL), you can still use a unique constraint on the composite-key columns if uniqueness is what you're trying to ensure. – Dan J Jan 24 '11 at 23:53
  • Not sure why you can't normalize your table without a composite primary key. Aren't keys NOT supposed to contain useful information? Worth reading this discussion: http://stackoverflow.com/questions/4737190/composite-primary-key-or-not – Wavel Jan 24 '11 at 23:54
  • @Wavel: there is not such a rule, however it helps a lot to use surrogate keys in place of natural keys, – Mchl Jan 24 '11 at 23:56
  • @Wavel I consider that good practice, yes. With InnoDB, there might occasionally be performance reasons to store something else in the primary key. – TehShrike Jan 24 '11 at 23:57
  • Well im using MyIsam. What is the point of md5ing the value? that will just take up more space then the value it's self and seems to add nothing. – andrew Jan 25 '11 at 00:29
  • The MD5 suggestion is **even more horrible** than the horrible suggestion. How can adding columns plus processing be justified ike this ! – PerformanceDBA Jan 25 '11 at 07:14
  • @PerformanceDBA: That's the kind of suggestions you get, when you try to complicate simple things. The resulting PK wuld have 32B while the current composite is 'only' 16B, so it's abviously worse from this point of view. The extra processing here should not add too much overhead (depending on the type of queries run). All in all I think it would work better than the second one BUT not as well as using the 'normal' table with 'normal' query. – Mchl Jan 25 '11 at 07:43
  • @Mchi. Sure. But the "normal" "tables" in the "normal" "databases" aren't databases at all, they are hamstrung and crippled by "normal" "keys". Databases are beyond the "normal" responders on SO, which is (let's face it) MySql coders at best. – PerformanceDBA Jan 25 '11 at 12:24
  • @Mchi. Hah! You must be "normal". – PerformanceDBA Jan 25 '11 at 12:51
  • @PerformanceDBA: Yeah, I normalized myself to the seventh normal form. – Mchl Jan 25 '11 at 13:20
  • @Mchl. That makes you a Null. – PerformanceDBA Jan 27 '11 at 12:55
  • @Mchl. Well, on any Standard SQL, you would be incomparable, but consistent. But half the SQLs and non-standard and inconsistent, so you would be different values. Then you have MyNonSQL which isn't SQL at all, there you could be anything ! – PerformanceDBA Jan 27 '11 at 13:26
0

You could create a stored procedure that takes '20001-1-2011-01-07 14:04:40' (a string) as an argument, then parse it and make the SELECT statement inside the procedure.

Marcus
  • 5,407
  • 3
  • 31
  • 54
  • I have never done anything like this before. Can you describe the procedure a little more so I can research this? – andrew Jan 24 '11 at 23:44
  • 1
    You could do that, but it would be overly complicated and add unnecessary inefficiencies. What you are trying to do is not a good idea, andrew. If you want to pass around a single string, parse that string apart in your code before you build the query. – TehShrike Jan 24 '11 at 23:46
  • If there is no way to do a shorthand select using the primary key then I will just pass all three arguments in the url. – andrew Jan 25 '11 at 00:13
0

No possible to do it like that. A stored procedure to pre-parse is one way to get this done. If you do not have to stick with this table design the I would suggest to change the primary key to a new column which you can set to autoincrement.

If you have to stick to this design then you can still add a new 'mapping' table which would as the name suggests map your combination to a primary key:

CREATE TABLE IF NOT EXISTS `bbb_mapping` (
  `YourPK` int(11) NOT NULL AUTO_INCREMENT,
  `OfficeCode` int(5) NOT NULL,
  `IssuerId` int(11) NOT NULL,
  `BulletinDtm` datetime NOT NULL
  PRIMARY KEY (`YourPK`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Using this approach you can join the mapping table with your original table while using YourPK in the querystring.

Cheers

Skorpioh
  • 1,355
  • 1
  • 11
  • 30
  • That's an interesting suggestion. If the primary key is unique and the three other columns comprise a unique key then this is just the same as creating another column with an autoincrementing primary key. – andrew Jan 25 '11 at 00:27
  • That's exactly the idea :D. From your table name I assume that you're trying to hack into PHPBB bulletin board. Adding an autoincrement into that table and changing the primary key could break the project (think of foreign keys) or maybe you would have to do dozens of changes in the code. If your only goal is to minimize the url/querystring then this seems as a nice workaround for it without the risk of breaking the thing. – Skorpioh Jan 25 '11 at 09:03