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.
