Re: Incrementing Primary Key

At 01:17 PM 10/28/2010, Bastien Koert wrote:

On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg <ethros@earthlink.net> wrote: At 03:40 AM 10/28/2010, Max E.K wrote:

From: "Ethan Rosenberg" <ethros@earthlink.net> To: RQuadling@googlemail.com, "Ethan Rosenberg" <ethros@earthlink.net> Cc: "php-db-lists.php.net" <php-db@lists.php.net> Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: Re: [PHP-DB] Incrementing Primary Key At 10:10 AM 10/27/2010, Richard Quadling wrote:

On 27 October 2010 14:11, Ethan Rosenberg <ethros@earthlink.net> wrote:

Dear List - Thanks for all your excellent help. I am setting up a database for medical research, which will be conducted at various sites. Â The sites will be identified

by a letter {A,B,C ....}. Â The

medical record number [primary key] Â will start at 1001 and increment by one(1) for each patient at each site; ie, A

1001, A1002, B1001, B1002 ......

How do I do this? Do I need a separate database for each site? Ethan

I'd use an INSERT trigger to generate the value. I use MS SQL - no idea what DB you are using - and so here are what I'd do ... Table: Sites UniqueID int identity(1,1) SiteCode char(1) LastMedicalRecordNumber int default 0 Table:MedicalRecords UniqueID int identity(1,1) SiteID int // Foreign key to Sites.UniqueID MedicalRecordNumber int default 0 The trigger would be something like [UNTESTED] ... CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS UPDATE Sites SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber WHERE UniqueID IN (Inserted.SiteID) UPDATE MedicalRecords SET MedicalRecordNumber = Sites.LastMedicalRecordNumber FROM INSERTED INNER JOIN MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID INNER JOIN Sites ON INSERTED.SiteID = Sites.UniqueID The app need not have any part is assigning something as important as the unqiue id of a row. That sort of integrity needs to be part of the database. The client app really wants to be as simple as possible. Using stored procedures and views (which are tuned once by the SQL Server) benefit the app in returning the required data faster and with less utilisation. Compare that against every identical query being compiled from scratch every single time. If you want to put the SiteCode on the MedicalRecord rather than the SiteID, you could. And then break the link between the MedicalRecords and Site tables. The trigger would use the SiteCode to link rather then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites. As far as the app goes? You tell it which of the available sites the medical record is for and insert it (along with any other user supplied data). The integrity is preserved by the DB. "Just doing my job, sir!" Richard. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

Thank you. I'm using MySQL, which I do not think has the ability to auto-increment a primary key from any value other than 1. Here is some pseudo code. Please help me to set it up properly. Table Intake

        SiteID char(2) primary key not null, //

This is A for site1, B for site 2....

        RecordNum  int(10,0) primary key not

null auto_increment, // increment starts from 10001

        etc.....

Thanks Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] Hi Ethan, This will set a new auto increment value for a table . ALTER TABLE RecordNum AUTO_INCREMENT=1001 Regards, Max. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

========== Max - Thanks. I must be doing something wrong, since the RecordNum starts from 1, and increments by 1. Maybe I am setting up the table incorrectly? Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php see: deleted because of spam filter.

To set an auto increment start value ALTER TABLE tbl AUTO_INCREMENT = 100; Bastien Cat, the other other white meat

Bastien - Thanks. It still does not work. This is what I have done to change the auto_increment: drop exiting_table;//called intake create table intake2 (Site char not null, Record int(10) not null auto_increment, BMI int(2),primary key(Site,Record)); alter table intake2 auto_increment=1000; insert into intake2 (Site,Record,BMI) values ('A',(null),15); insert into intake2 (Site,Record,BMI) values ('A',(null),18); insert into intake2 (Site,Record,BMI) values ('A',(null),13); mysql> select * from intake2; +------+--------+------+ | Site | Record | BMI | +------+--------+------+

| A    |      1 |   15 |
| A    |      2 |   18 |
| A    |      3 |   19 |

+------+--------+------+ 3 rows in set (0.00 sec) What is my mistake? Ethan MySQL 5.1 PHP 5 Linux [Debian (sid)]