Unanswered: Creating Primary Key after CREATE TABLE: To create Sequence when using ALTER TABLE
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
For a table testing, 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"
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?