Re: Creating Primary Key after CREATE TABLE: Is Sequence created?

From: Elliot <yields(dot)falsehood(at)gmail(dot)com>
To: mdr <monosij(dot)forums(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Creating Primary Key after CREATE TABLE: Is Sequence created?
Date: 2013-09-27 19:04:14
Message-ID: 5245D6AE.2080201@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 2013-09-27 14:27, mdr wrote:
> I had a question on creating PK with alter table, after table is created.
>
> I understand I create a PK id during create table by stating id as follows:
> id serial primary key
>
> It implicitly creates index and the sequence testing_id_seq to be associated
> with the id field.
> I can list the sequence with \ds.
>
> ...
> However if I create a primary key with alter table primary key as in:
> import_dbms_db=> alter table testing ADD CONSTRAINT pkid PRIMARY KEY (id);
> NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pkid" for
> table "testing"
> ALTER TABLE
> import_dbms_db=> \ds
> No relations found.
>
> It does not create a sequence.
Correct - it is the type of the column ("serial") in your create table
statement that automatically creates the sequence and attaches it to the
column, not the primary key constraint.

> Also during creating indexes (primary, secondary or foreign) am I allowed to
> create indexes with same name but on different tables? Or do index names
> have to be different across tables? probably good programming practice as
> well to have different index names across tables even if allowed?
Yes, index names must be unique within a schema. I usually name indexes
something like "idx_<tablename>_<indexed column(s) name(s)>" to
differentiate everything.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message mdr 2013-09-27 19:40:52 Re: Creating Primary Key after CREATE TABLE: Is Sequence created?
Previous Message David Johnston 2013-09-27 18:59:56 Re: Creating Primary Key after CREATE TABLE: Is Sequence created?