Обсуждение: A simple question

Hello,
I wonder if postgres autocreates an index on foreign or primary key fields.

For example:

create table t1 (
  f1 integer,
  f2 varchar,
  primary key (f1));

create table t2 (
  f1 integer,
  f2 integer,
  primary key (f1),
  foreign key (f2) references t1(f1)
);

In this sample, "t1" has a primary key, and "t2" both (primary and foreign
keys).
Does postgres create each indexes for the primary key of "t1" and "t2" ?

and indexes for "f2" field in "t2" ?

I expleain, there are several DBMS where autocreates these indexes for you,
and others that shows you an alert, for example, if you don't create an unique
index for the primary key.

Thanks.

You can easily test yourself:

test=# \d t1
           Table "public.t1"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 f1     | integer           | not null
 f2     | character varying |
Indexes: t1_pkey primary key btree (f1)

test=# \d t2
      Table "public.t2"
 Column |  Type   | Modifiers
--------+---------+-----------
 f1     | integer | not null
 f2     | integer |
Indexes: t2_pkey primary key btree (f1)
Foreign Key constraints: $1 FOREIGN KEY (f2) REFERENCES t1(f1) ON UPDATE
NO ACTION ON DELETE NO ACTION

test=#

So the answer is: yes for PK, no for FK in PG.


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of juanmime@ono.com
Sent: Wednesday, March 16, 2005 8:57 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] A simple question

Hello,
I wonder if postgres autocreates an index on foreign or primary key
fields.

For example:

create table t1 (
  f1 integer,
  f2 varchar,
  primary key (f1));

create table t2 (
  f1 integer,
  f2 integer,
  primary key (f1),
  foreign key (f2) references t1(f1)
);

In this sample, "t1" has a primary key, and "t2" both (primary and
foreign
keys).
Does postgres create each indexes for the primary key of "t1" and "t2" ?

and indexes for "f2" field in "t2" ?

I expleain, there are several DBMS where autocreates these indexes for
you,
and others that shows you an alert, for example, if you don't create an
unique
index for the primary key.

Thanks.



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly