All,
I am developing a service in MySQL that models a service I've already
built in PostgreSQL. I'm trying to port over some of my ideas from that
platform to MySQL. Here's the setup:
Let's say I have 2 tables: 'account' and 'widget'. Each of these tables
have a primary key but the widget table references the account table
with a foreign key on account_id:
----------
CREATE TABLE `account` (
`account_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`label` VARCHAR(64) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
PRIMARY KEY (`account_id`),
UNIQUE KEY `account_id` (`account_id`)
)
ENGINE=InnoDB AUTO_INCREMENT=1
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE TABLE `widget` (
`widget_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`label` VARCHAR(64) COLLATE latin1_swedish_ci NOT NULL DEFAULT '',
`account_id` INTEGER(11) UNSIGNED NOT NULL,
`widget_number` INTEGER(11) UNSIGNED NOT NULL,
PRIMARY KEY (`widget_id`),
UNIQUE KEY `widget_id` (`widget_id`),
UNIQUE KEY `widget_number` (`account_id`, `widget_number`),
KEY `account_id` (`account_id`),
CONSTRAINT `widget_fk_account_id` FOREIGN KEY (`account_id`)
REFERENCES `account` (`account_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
)
ENGINE=InnoDB AUTO_INCREMENT=1
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
----------
The problem is that I don't want customers to see the 'widget_id' column
on the url like this:
http://.../app/widget/edit.php?widget_id=123456
Because if they see this, they can use this information to deduce how
many widgets exist in my database. Instead, I want to add another
column to the widget table called 'widget_number' that contains an
AUTO_INCREMENT column which is based on the 'account_id' in that table.
What this means is that every account contains a widget number 1000.
And if you add another widget, you get widget number 1001, etc. This
way, the url will look like this:
http://.../app/widget/edit.php?widget_number=1000
And if I combine widget number 1000 with the account_id, I can uniquely
identify an entry in the database. In other words, I have a new primary
key candidate of (account_id, widget_number).
I would use this combo as the primary key, but I hate doing joins with
multiple primary keys, so I'll also keep the widget_id for the purpose
of making joins easier.
My question is how can I get MySQL to automatically assign the
widget_number values? I want the first value for every account ID to be
1000 and increment from there. I almost need a trigger that does
something like this:
SELECT COALESCE(MAX(widget_number) + 1, 1000)
INTO NEW.widget_number
FROM widget
WHERE account_id = NEW.account_id;
I don't think the 'MAX' is optimized, though and maybe there is a
better, more robust way to do this which is already built into MySQL
that I don't know about.
I am using InnoDB, so the solution needs to be transaction safe. Help
would be appreciated.
-- Dante
----------
D. Dante Lorenso
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
