Thread: Composite Keys
First, can a primary key field be only one? I mean can I define a primary containing two fields. I have a table called "Payments" where there are two fields, "ReceiptNo" and "PaymentDate". If I take "ReceiptNo" only as a primary key then chances are of "primary key violation". Because in some places the receipt books have numbers that start with 1. I mean Receipt Book 1 nos. 1-100. Receipt Book 2 nos. 1-100, and so on. So I want to take ReceiptNo and PaymentDate as primary key field. Is it possible to define composite key in PGSQL. Please also suggest if any other approach would be fine. -- View this message in context: http://www.nabble.com/Composite-Keys-tf3282722.html#a9131229 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
RPK wrote:
> First, can a primary key field be only one? I mean can I define a primary
> containing two fields.
As many as you like :)
>
> I have a table called "Payments" where there are two fields, "ReceiptNo" and
> "PaymentDate". If I take "ReceiptNo" only as a primary key then chances are
> of "primary key violation". Because in some places the receipt books have
> numbers that start with 1. I mean Receipt Book 1 nos. 1-100. Receipt Book 2
> nos. 1-100, and so on.
>
> So I want to take ReceiptNo and PaymentDate as primary key field. Is it
> possible to define composite key in PGSQL. Please also suggest if any other
> approach would be fine.
CREATE TABLE payments (receiptno serial, paymentdate date, primary key
(reciptno,paymentdate))
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Em Sábado 24 Fevereiro 2007 03:28, RPK escreveu: > First, can a primary key field be only one? I mean can I define a primary > containing two fields. > > I have a table called "Payments" where there are two fields, "ReceiptNo" > and "PaymentDate". If I take "ReceiptNo" only as a primary key then chances > are of "primary key violation". Because in some places the receipt books > have numbers that start with 1. I mean Receipt Book 1 nos. 1-100. Receipt > Book 2 nos. 1-100, and so on. > > So I want to take ReceiptNo and PaymentDate as primary key field. Is it > possible to define composite key in PGSQL. Please also suggest if any other > approach would be fine. It is possible, but it looks like you'd really want ReceiptBookNo + ReceiptNo as a primary key... If you use that you can then control the number of left recipes on each receipt book or when to order new books. If you use the date take special care with long term payments so that you don't have two recipes "10" scheduled for the same day. -- Jorge Godoy <jgodoy@gmail.com>
Jorge, For other tables I have ID field which is incremented by sequence. But for this table, there is not ID field. Receipt No will be incremented by finding the max value from the existing Receipt Nos. corresponding to that Book No. This case has a drawback as compared to the sequences in other tables. Sequences are automatically handled by the database if two users simultaneously enter data. But for the Receipts table where there is no need to define a sequence, one user will find the Max(ReceiptNo) and type in the rest of the entries. So there is a chance that in the meantime another user on a different machine will also get the same max(ReceiptNo) until the record of the previous user gets saved. So how to solve this problem? -- View this message in context: http://www.nabble.com/Composite-Keys-tf3282722.html#a9164794 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
RPK wrote: > > Jorge, > > For other tables I have ID field which is incremented by sequence. But for > this table, there is not ID field. Receipt No will be incremented by finding > the max value from the existing Receipt Nos. corresponding to that Book No. > This case has a drawback as compared to the sequences in other tables. > Sequences are automatically handled by the database if two users > simultaneously enter data. But for the Receipts table where there is no need > to define a sequence, one user will find the Max(ReceiptNo) and type in the > rest of the entries. So there is a chance that in the meantime another user > on a different machine will also get the same max(ReceiptNo) until the > record of the previous user gets saved. > > So how to solve this problem? Lock the table beforehand. Only one user can be getting the max(ReceiptNo) that way. Alternatively, you could use userlocks, so that you can lock, generate the number, unlock. And you can use it to lock that particular BookNo, not the whole table. (In Postgres 8.2 the facility is called "advisory locks" and comes with the core code; in older releases it's in contrib and it's called "userlock"). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro, I am using VB.NET. How to enable locking from within VB.NET for PostgreSQL? Which command need to be executed? Alvaro Herrera-7 wrote: > >>Lock the table beforehand. Only one user can be getting the >>max(ReceiptNo) that way. > >>Alternatively, you could use userlocks, so that you can lock, generate >>the number, unlock. And you can use it to lock that particular BookNo, >>not the whole table. (In Postgres 8.2 the facility is called "advisory >>locks" and comes with the core code; in older releases it's in contrib >>and it's called "userlock"). > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > -- View this message in context: http://www.nabble.com/Composite-Keys-tf3282722.html#a9166441 Sent from the PostgreSQL - general mailing list archive at Nabble.com.