On Thu, Oct 28, 2010 at 1:00 PM, Ethan Rosenberg <ethros@xxxxxxxxxxxxx> wrote:
> At 03:40 AM 10/28/2010, Max E.K wrote:
>
>> From: "Ethan Rosenberg" <ethros@xxxxxxxxxxxxx>
>> To: RQuadling@xxxxxxxxxxxxxx, "Ethan Rosenberg" <ethros@xxxxxxxxxxxxx>
>> Cc: "php-db-lists.php.net" <php-db@xxxxxxxxxxxxx>
>> Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin
>> / Bern / Rome / Stockholm / Vienna
>> Subject: Re: Incrementing Primary Key
>>
>> At 10:10 AM 10/27/2010, Richard Quadling wrote:
>> >On 27 October 2010 14:11, Ethan Rosenberg <ethros@xxxxxxxxxxxxx> 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 http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
To set an auto increment start value
ALTER TABLE tbl AUTO_INCREMENT = 100;
--
Bastien
Cat, the other other white meat
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
