At 03:40 AM 10/28/2010, Max E.K wrote:

From: "Ethan Rosenberg" <>
To:, "Ethan Rosenberg" <>
Cc: "" <>
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 <> 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
>  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
>    MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID
>    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
>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 Quadling
>Twitter : EE : Zend
>@RQuadling : :

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



MySQL 5.1  PHP 5  Linux [Debian (sid)]

Hi Ethan,

This will set a new auto increment value for a table .




PHP Database Mailing List (
To unsubscribe, visit:

Max -


I must be doing something wrong, since the RecordNum starts from 1, and increments by 1. Maybe I am setting up the table incorrectly?


MySQL 5.1 PHP 5 Linux [Debian (sid)]

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to