Обсуждение: Should I add a Index Key in this case ?

Поиск
Список
Период
Сортировка

Should I add a Index Key in this case ?

От
Karen Goh
Дата:
Hi Experts,

I have a use case as follows : 

 add constraints to the database so that no two reservations for the same viewing may refer to the same seat.

So, say I have a primary key like this in the table A:


    SEAT_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1
),
    CONSTRAINT A_pkey PRIMARY KEY (SEAT_id))
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

So, basically I would like to create a SEAT_Viewing_Id.

In this case, do I create a Index Key or ?

How should I construct or rather alter my table A to accomodate this SEAT_Viewing_Id ?

Hope someone can tell me how.

Furthermore, whenever an insertion is done via WebApp, I would have to insert the Index key as well or does PostgreSQL
willhave a way to increment the Index key which is the SEAT_Viewing_Id at the same time?
 

Thanks & regards,
Karen

Re: Should I add a Index Key in this case ?

От
Steve Midgley
Дата:


On Wed, Oct 16, 2019 at 7:36 AM Karen Goh <karenworld@yahoo.com> wrote:
Hi Experts,

I have a use case as follows :

 add constraints to the database so that no two reservations for the same viewing may refer to the same seat.

So, say I have a primary key like this in the table A:


    SEAT_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    CONSTRAINT A_pkey PRIMARY KEY (SEAT_id))
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

So, basically I would like to create a SEAT_Viewing_Id.

In this case, do I create a Index Key or ?

How should I construct or rather alter my table A to accomodate this SEAT_Viewing_Id ?

Hope someone can tell me how.

Furthermore, whenever an insertion is done via WebApp, I would have to insert the Index key as well or does PostgreSQL will have a way to increment the Index key which is the SEAT_Viewing_Id at the same time?

Thanks & regards,
Karen


I'm not sure I understand exactly what you want to do, but it sounds like you are want to create a second field/column in your table named "SEAT_viewing_id" and you want that field to auto-increment independently from the primary key? If so, you can consider the "serial" datatype as possiblye meeting your needs: https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

So just `alter table`, to add your new field, and make its datatype `serial`.

Apologies if I misunderstood your question,
Steve

Re: Should I add a Index Key in this case ?

От
Karen Goh
Дата:
Hi Steve,

My question is should I use index on the Seat_Viewing_id ?

I have no experience in using Index hence I asked if it should be made auto-incremental?

Kindly advise how should I alter my existing table to have index. 

Tks!


Sent from Yahoo Mail for iPhone

On Wednesday, October 16, 2019, 11:26 PM, Steve Midgley <science@misuse.org> wrote:



On Wed, Oct 16, 2019 at 7:36 AM Karen Goh <karenworld@yahoo.com> wrote:
Hi Experts,

I have a use case as follows :

 add constraints to the database so that no two reservations for the same viewing may refer to the same seat.

So, say I have a primary key like this in the table A:


    SEAT_id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    CONSTRAINT A_pkey PRIMARY KEY (SEAT_id))
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

So, basically I would like to create a SEAT_Viewing_Id.

In this case, do I create a Index Key or ?

How should I construct or rather alter my table A to accomodate this SEAT_Viewing_Id ?

Hope someone can tell me how.

Furthermore, whenever an insertion is done via WebApp, I would have to insert the Index key as well or does PostgreSQL will have a way to increment the Index key which is the SEAT_Viewing_Id at the same time?

Thanks & regards,
Karen


I'm not sure I understand exactly what you want to do, but it sounds like you are want to create a second field/column in your table named "SEAT_viewing_id" and you want that field to auto-increment independently from the primary key? If so, you can consider the "serial" datatype as possiblye meeting your needs: https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

So just `alter table`, to add your new field, and make its datatype `serial`.

Apologies if I misunderstood your question,
Steve

Re: Should I add a Index Key in this case ?

От
Rob Sargent
Дата:


On Oct 16, 2019, at 9:47 AM, Karen Goh <karenworld@yahoo.com> wrote:

Hi Steve,

My question is should I use index on the Seat_Viewing_id ?

I have no experience in using Index hence I asked if it should be made auto-incremental?

Kindly advise how should I alter my existing table to have index. 

Tks!

alter table doc here: https://www.postgresql.org/docs/10/sql-altertable.html
you will likely need an index on that new column, though it’s not clear to how you wish to fill in the value for existing rows.  Perhaps you should share your current table definition?
I think you may need a seat_viewing table, in which you place the id of the viewed seats.  You would want a unique index on the new table using the existing seat id but you do NOT want the column to be of type serial, just (big?) integer.