Creating Primary Key after CREATE TABLE: Is Sequence created?

From: mdr <monosij(dot)forums(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Creating Primary Key after CREATE TABLE: Is Sequence created?
Date: 2013-09-27 18:27:29
Message-ID: 1380306449060-5772633.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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.

So I am assuming I have to create the sequence and associate it with the
column separately as well?

Is there a way to create primary key with the alter table to allow the
sequence to be created automatically and associated with the primary key?

I did not find anything on this so just wanted to confirm - so I write my
scripts accordingly.

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?

Thank you for your help and suggestions.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-Primary-Key-after-CREATE-TABLE-Is-Sequence-created-tp5772633.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sergey Konoplev 2013-09-27 18:37:13 Re: ENUM drop label workaround
Previous Message John R Pierce 2013-09-27 18:16:33 Re: Trying to create DB / user to import some data