RE: Incrementing Primary Key

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Actually, after looking at it further, you have the records set to only have
not null values yet you are passing a null value to it.

-----Original Message-----
From: Ethan Rosenberg [mailto:ethros@xxxxxxxxxxxxx] 
Sent: Thursday, October 28, 2010 2:02 PM
To: Bastien Koert
Cc: Max E.K; php-db-lists.php.net; RQuadling@xxxxxxxxxxxxxx
Subject: 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@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: 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)] 



--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux