Thread: Creating Primary Key after CREATE TABLE: Is Sequence created?
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.
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. "PRIMARY KEY" implicitly creates an index "serial" (i.e., the column type) implicitly creates a sequence (of type integer; bigserial creates a biginteger sequence) psuedo-sql: CREATE TABLE a (id serial); ALTER TABLE a ADD CONSTRAINT PRIMARY KEY (id); The first creates the serial; the second creates the index and unique constraint. Should be equivalent to: CREATE TABLE a (id serial PRIMARY KEY); David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-Primary-Key-after-CREATE-TABLE-Is-Sequence-created-tp5772633p5772636.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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.
Hi David and John - Thank you for your answers on the SERIAL, SEQUENCE and thus PRIMARY KEY. I did not realize there was a column of type SERIAL that creates a SEQUENCE. However, now I have a different question. Is it possible to create a column of type SQL:2011 types (INTEGER or such) and then connect a SEQUENCE to it and make that column a PRIMARY KEY - without creating a column of type SERIAL? It seems column of type SERIAL is specific to Postgres and will make my script Postgres dependent? Just in case I decide to go to MySQL. Most likely not, but just wanted to manage to SQL:2011. Thank you again. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-Primary-Key-after-CREATE-TABLE-Is-Sequence-created-tp5772633p5772642.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 9/27/2013 12:40 PM, mdr wrote:
> Is it possible to create a column of type SQL:2011 types (INTEGER or such)
> and then connect a SEQUENCE to it and make that column a PRIMARY KEY -
> without creating a column of type SERIAL?
yes, its just more work.
something like...
CREATE TABLE fred (id integer, val character varying(100))
PRIMARY KEY (id);
CREATE SEQUENCE fred_id_seq OWNED BY fred.id;
ALTER TABLE fred ALTER COLUMN id SET DEFAULT
nextval('fred_id_seq'::regclass);
nextval() and OWNED BY are both postgres extensions.
>
> It seems column of type SERIAL is specific to Postgres and will make my
> script Postgres dependent?
>
> Just in case I decide to go to MySQL. Most likely not, but just wanted to
> manage to SQL:2011.
MySQL uses some kind of nonstandard 'autoincrement' attribute rather
than SEQUENCE
--
john r pierce 37N 122W
somewhere on the middle of the left coast
On 9/27/2013 12:40 PM, mdr wrote: > just wanted to > manage to SQL:2011. afaik, *nothing* completely implements SQL:2011 (or any prior SQL standard). The standard are unwieldy messes cobbled together out of various vendors wishlists mixed in with academic wishful thinking. if you program to the lowest common denominator of the various database servers, you're application will perform poorly on all of them. -- john r pierce 37N 122W somewhere on the middle of the left coast
Thanks again John. Will try it out. So I have to rely in 1 statement that is Postgres specific. That still works for me. Also I can create the PRIMARY KEY as an alter table and not be part of CREATE TABLE right? So let me see how the trials go. Thank you again John. Also Elliot for your last answer. Mono -- View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-Primary-Key-after-CREATE-TABLE-Is-Sequence-created-tp5772633p5772649.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Ok I understand about not programming to LCD. I just initially wanted to play around with creating tables / indexes / fks and import some data before moving to next steps. I did want to have some control over namimg conventions. For eg. with creating PK id and sequence with 1 statement as: id serial primary key I have a vague idea how the primary key and sequence was named but no control on it. However if I could control the naming convention from here I would be ok with it. Would there be a way to control the naming of the id and sequence from the create table statement? Thank you. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-Primary-Key-after-CREATE-TABLE-Is-Sequence-created-tp5772633p5772651.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09/27/2013 01:16 PM, Monosij wrote: > Ok I understand about not programming to LCD. > > I just initially wanted to play around with creating tables / indexes / fks > and import some data before moving to next steps. > > I did want to have some control over namimg conventions. > > For eg. with creating PK id and sequence with 1 statement as: > id serial primary key > I have a vague idea how the primary key and sequence was named but no > control on it. > > However if I could control the naming convention from here I would be ok > with it. > > Would there be a way to control the naming of the id and sequence from the > create table statement? Using serial, remembering that serial is basically a macro that does what is shown here: http://www.postgresql.org/docs/9.3/interactive/datatype-numeric.html#DATATYPE-SERIAL Name your table and id column to get the desired sequence name. Otherwise do as shown in the expanded example in the link above and wrap multiple statements in a transaction and create the sequence and id as you want. > > Thank you. > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-Primary-Key-after-CREATE-TABLE-Is-Sequence-created-tp5772633p5772651.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com
Thanks Adrian. Will likely follow the transaction approach then. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-Primary-Key-after-CREATE-TABLE-Is-Sequence-created-tp5772633p5772665.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.