Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2013
    Posts
    23

    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"
    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by mdr View Post
    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?
    Code:
    create table foo
    (
       id integer not null,
       ... other columns
       constraint pk_foo primary key (id)
    );
    create sequence foo_id_seq
      owned by foo.id;
    I did not find anything on this
    This is suprising as this is all documented in the manual:



    Also during creating indexes (primary, secondary or foreign) am I allowed to create indexes with same name but on different tables?
    No. The index name must be unique across all objects (you can't have a table and an index with the same name).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Jun 2013
    Posts
    23
    Thank you Shammat. I was not able to see the 'owned by' for the sequence.

    It would be great to have a few examples that go along with the documentations.

    If there was someone I could reach to help with that please let me know.

    Thanks again.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •