[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
From: "Franco Bruno Borghesi" <fborghesi () gmail ! com>
Date: 2006-05-24 13:04:20
Message-ID: e13c14ec0605240604j4bd6a5abxa6cb846f1fdb0a18 () mail ! gmail ! com
[Download RAW message or body]
Well, you could add a serial column. I'll tell you how, but I haven't tested
the code, so be sure to check it! And using BEGIN and COMMIT/ROLLBACK to
delimit transactions would not be a bad idea at all ;-)
To add a serial column, just write:
--create new serial field
ALTER TABLE md_customer ADD id SERIAL;
If you check your table now, you will see that your new 'id' column contains
correlative values. If this is what you wanted, you could update every tabe
referencing md_customer, like this:
--drop foreign key on remote table
ALTER TABLE xxx DROP CONSTRAINT xxx_fk;
--set old pk values to the value in the 'id' field just created
UPDATE xxx SET fk_field=md_customer.id FROM md_customer CU WHERE
CU.md_customeridpk=xxx.fk_field;
Check if everything is ok now. If it is, then recreate your foreign key,
drop your old pk and rename the new one:
--restore fk on remote table
ALTER TABLE xxx ADD CONSTRAINT xxx_fk FOREIGN KEY (fk_field) REFERENCES
md_customer(id) ON DELETE ... ON UPDATE ...;
--drop old pk
ALTER TABLE md_customer DROP md_customeridpk CASCADE;
--rename id to md_customeridpk
ALTER TABLE md_customer RENAME id TO md_customeridpk;
--create pk
ALTER TABLE md_customer ADD CONSTRAINT md_customer_pk PRIMARY
KEY(md_customeridpk).
That should be it.
Hope it helps.
On 5/24/06, andi <andi@mobile-elab.com> wrote:
>
> Dear friends,
>
>
>
> I have table
>
> MD_CUSTOMER
>
> *MD_CUSTOMERIDPK* integer primary key
>
> *NAME *varchar
>
>
>
>
>
> But my primary key is not in correct order like
>
> *MD_CUSTOMER*
>
> MD_CUSTOMERIDPK NAME
>
> 10
> ANDI
>
> 33
> TESTER
>
> 100 KKK
>
>
>
> , so I want to make other primary key to generate sequences 1, 2, 3, … and
> in MS SQL SERVER 2005
>
> I can with Rank() function , but in Postgres how ?
>
>
>
> PLEASE any one can help me, I am really appreciate.
>
>
>
> Best regards
>
>
>
> Andi kusnadi
>
[Attachment #3 (text/html)]
Well, you could add a serial column. I'll tell you how, but I haven't tested the \
code, so be sure to check it! And using BEGIN and COMMIT/ROLLBACK to delimit \
transactions would not be a bad idea at all ;-)<br><br>To add a serial column, just \
write: <br>
<br>--create new serial field<br>
ALTER TABLE md_customer ADD id SERIAL;<br>
<br>
If you check your table now, you will see that your new 'id' column contains \
correlative values. If this is what you wanted, you could update every tabe \
referencing md_customer, like this:<br><br>--drop foreign key on remote table \
<br>ALTER TABLE xxx DROP CONSTRAINT xxx_fk;<br><br>--set old pk values to the value \
in the 'id' field just created<br>UPDATE xxx SET fk_field=md_customer.id FROM \
md_customer CU WHERE CU.md_customeridpk=xxx.fk_field;<br><br> Check if everything is \
ok now. If it is, then recreate your foreign key, drop your old pk and rename the new \
one:<br><br>--restore fk on remote table<br>ALTER TABLE xxx ADD CONSTRAINT xxx_fk \
FOREIGN KEY (fk_field) REFERENCES md_customer(id) ON DELETE ... ON UPDATE ...; \
<br><br>--drop old pk<br>ALTER TABLE md_customer DROP md_customeridpk \
CASCADE;<br><br>--rename id to md_customeridpk<br>ALTER TABLE md_customer RENAME id \
TO md_customeridpk;<br><br>--create pk<br>ALTER TABLE md_customer ADD CONSTRAINT \
md_customer_pk PRIMARY KEY(md_customeridpk). <br><br>That should be it.<br>Hope it \
helps.<br> <br><br><span class="gmail_quote">On 5/24/06, <b \
class="gmail_sendername">andi</b> <<a \
href="mailto:andi@mobile-elab.com">andi@mobile-elab.com</a>> wrote:</span>
<font face="Arial" size="2"><span style="font-size: 10pt; font-family: \
Arial;"></span></font><div><blockquote class="gmail_quote" style="border-left: 1px \
solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> <div><div \
link="blue" vlink="purple" lang="EN-US"><div><p><font face="Arial" size="2"><span \
style="font-size: 10pt; font-family: Arial;">Dear friends,</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: \
Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">I \
have table</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: \
Arial;">MD_CUSTOMER</span></font></p>
<p><b><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; \
font-weight: bold;">MD_CUSTOMERIDPK</span></font></b><font face="Arial" \
size="2"><span style="font-size: 10pt; font-family: Arial;"> integer primary \
key</span></font></p>
<p><b><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; \
font-weight: bold;">NAME &n \
bsp; \
</span></font></b><font face="Arial" size="2"><span style="font-size: 10pt; \
font-family: Arial;"> varchar</span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: \
Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: \
Arial;"> </span></font></p>
<p><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">But \
my primary key is not in correct order like</span></font></p>
<p><b><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; \
font-weight: bold;">MD_CUSTOMER</span></font></b></p>
<p><font face="Times New Roman" size="3"><span style="font-size: \
12pt;">MD_CUSTOMERIDPK \
NAME</span></font></p>
<p style="margin-left: 2.75in; text-indent: -2.5in;"><font face="Times New Roman" \
size="3"><span style="font-size: 12pt;"><span>10<font face="Times New Roman" \
size="1"><span>   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; &nb \
sp;
</span></font></span></span></font>ANDI</p>
<p style="margin-left: 2.75in; text-indent: -2.5in;"><font face="Times New Roman" \
size="3"><span style="font-size: 12pt;"><span>33<font face="Times New Roman" \
size="1"><span>   \
; & \
nbsp; &nb \
sp;   \
; & \
nbsp; &nb \
sp;
</span></font></span></span></font>TESTER</p>
<p style="margin-left: 0.25in;"><font face="Times New Roman" size="3"><span \
style="font-size: 12pt;">100 &nbs \
p; \
&n \
bsp; \
KKK</span></font></p>
<p><font face="Times New Roman" size="3"><span style="font-size: \
12pt;"> </span></font></p>
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;">, so I want \
to make other primary key to generate sequences 1, 2, 3, … and in MS SQL SERVER \
2005</span></font></p>
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;">I can with \
Rank() function , but in Postgres how ?</span></font></p>
<p><font face="Times New Roman" size="3"><span style="font-size: \
12pt;"> </span></font></p>
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;">PLEASE any \
one can help me, I am really appreciate.</span></font></p>
<p><font face="Times New Roman" size="3"><span style="font-size: \
12pt;"> </span></font></p>
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;">Best \
regards</span></font></p>
<p><font face="Times New Roman" size="3"><span style="font-size: \
12pt;"> </span></font></p>
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;">Andi \
kusnadi</span></font></p>
</div>
</div>
</div></blockquote></div><br>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic