Thread: Re: [SQL] database design SQL prob.
Currently, in postgreSQL, primary keys are created as a UNIQUE index on the
field(s) that form the primary key.
This means that there is no difference between explicitely declaring a
PRIMARY KEY in your table definition or using the CREATE UNIQUE INDEX
command.
There is one caveat to this, CREATE UNIQUE INDEX (at least in my PG 6.4.0)
will allow NULLs to be inserted in the indexed field (theoretically, all
NULLs could be different) whereas declaring that field as a primary key in
the table definition will ensure that no NULLs can be inserted (because if
there are several NULLs, you cannot use the field to uniquely identify an
entry).
So to have member_id as you primary key and ensure uniqueness of the
combination of firstname, lastname, adress, zipcode you get:
CREATE TABLE "member" ("member_id" int4 DEFAULT nextval ( 'lid_id_seq' ) UNIQUE NOT NULL,"firstname" text, -- NOT NULL?
youmust decide"lastnaam" text, -- Ditto (typo? should it be lastname?)"adress" text, -- Ditto (typo? should it be
address?)"zipcoder"character(4), -- Ditto"telephone" text,"email" text,"registration_date" date DEFAULT current_date
NOTNULL,"student_id" text,"dep_id" text,"password" text NOT NULL,"validated" bool DEFAULT 'f' NOT NULL,PRIMARY KEY
(member_id)
);
And then you create the unique index on the other fields:
CREATE UNIQUE INDEX member_fn_ln_ad_zc_idx ON member (firstname, lastnaam,
adress, zipcode);
You can get more info by typing \h create index and \h create table in psql.
Regards,
Stuart.
>The idea of the table below is to keep track of members. They have to register
>themself so I want to prevent them from subscribing twice. That's why I used a
>primary key on the fields firstname, lastname, adres, zipcode. But I would
>really want member_id to be my primary key as the table is referenced by other
>tables. Can I make firstname, lastname... a unique value in another way?
>Like constraint UNIQUE (firstname, lastname,adres,zipcode)
>I just made that last one up but is it possible to enforce the uniqueness of a
>couple of fields together?
>
>CREATE TABLE "member" (
> "member_id" int4 DEFAULT nextval ( 'lid_id_seq' ) UNIQUE NOT NULL,
> "firstname" text,
> "lastnaam" text,
> "adress" text,
> "zipcoder" character(4),
> "telephone" text,
> "email" text,
> "registration_date" date DEFAULT current_date NOT NULL,
> "student_id" text,
> "dep_id" text,
> "password" text NOT NULL,
> "validated" bool DEFAULT 'f' NOT NULL,
> PRIMARY KEY (firstname, lastname, adres, zipcode));
+--------------------------+--------------------------------------+
| Stuart C. G. Rison | Ludwig Institute for Cancer Research |
+--------------------------+ 91 Riding House Street |
| N.B. new phone code!! | London, W1P 8BT |
| Tel. +44 (0)207 878 4041 | UNITED KINGDOM |
| Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk |
+--------------------------+--------------------------------------+
Thus spake Stuart Rison
> Currently, in postgreSQL, primary keys are created as a UNIQUE index on the
> field(s) that form the primary key.
>
> This means that there is no difference between explicitely declaring a
> PRIMARY KEY in your table definition or using the CREATE UNIQUE INDEX
> command.
Not completely accurate. Create some tables using both methods then
run the following query.
SELECT pg_class.relname, pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.oid =
pg_attribute.attrelidAND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND
pg_index.indisprimary = 't';
This will give you a list of the primary keys if you declare them as
primary at creation time. The ones created with just a unique index
won't be displayed.
While I am on the subject, anyone know how to enhance the above query
to display all the fields when a complex primary key is defined? The
above assumes that all primary keys are one field per table.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
At 12:12 +0300 on 23/07/1999, Stuart Rison wrote: > This means that there is no difference between explicitely declaring a > PRIMARY KEY in your table definition or using the CREATE UNIQUE INDEX > command. > There is one caveat to this, CREATE UNIQUE INDEX (at least in my PG 6.4.0) > will allow NULLs to be inserted in the indexed field (theoretically, all > NULLs could be different) whereas declaring that field as a primary key in > the table definition will ensure that no NULLs can be inserted (because if > there are several NULLs, you cannot use the field to uniquely identify an > entry). To be more exact, primary keys are defined as NOT NULL implicitly. If you want to emulate primary keys, you have to define the key as NOT NULL as well as define a unique index on it. But for the original question: define the primary key on the id field, and a unique index on the combination of fields that you want to be unique. Now everything makes sense. The field used for reference, which is by definition the primary key of the table, is indeed defined as primary key. The combination of fields which is not used for references, but which needs to be unique, is defined as unique. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
"D'Arcy J.M. Cain" wrote:
>
> Not completely accurate. Create some tables using both methods then
> run the following query.
>
> SELECT pg_class.relname, pg_attribute.attname
> FROM pg_class, pg_attribute, pg_index
> WHERE pg_class.oid = pg_attribute.attrelid AND
> pg_class.oid = pg_index.indrelid AND
> pg_index.indkey[0] = pg_attribute.attnum AND
> pg_index.indisprimary = 't';
>
> This will give you a list of the primary keys if you declare them as
> primary at creation time. The ones created with just a unique index
> won't be displayed.
>
> While I am on the subject, anyone know how to enhance the above query
> to display all the fields when a complex primary key is defined? The
> above assumes that all primary keys are one field per table.
>
However, if you create table with primary key, for example
create table tab(
id int4 primary key,
...
);
and make dump of database, it will write in dump file
create table tab(
id int4,
...
);
create unique index "tab_pkey" on "tab" using btree ("id");
So, after dump / restore difference between primary key and unique index
disappears.
Is it right?
Sincerely yours, Yury.
don.web-page.net, ICQ 11831432
Thus spake Don Yury
> > Not completely accurate. Create some tables using both methods then
> However, if you create table with primary key, for example
>
> create table tab(
> id int4 primary key,
> ...
> );
>
> and make dump of database, it will write in dump file
>
> create table tab(
> id int4,
> ...
> );
> create unique index "tab_pkey" on "tab" using btree ("id");
So it does. I thought that this was fixed in 6.5 but it seems not. Is
this on the TODO list?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> Thus spake Don Yury
> > > Not completely accurate. Create some tables using both methods then
> > However, if you create table with primary key, for example
> >
> > create table tab(
> > id int4 primary key,
> > ...
> > );
> >
> > and make dump of database, it will write in dump file
> >
> > create table tab(
> > id int4,
> > ...
> > );
> > create unique index "tab_pkey" on "tab" using btree ("id");
>
> So it does. I thought that this was fixed in 6.5 but it seems not. Is
> this on the TODO list?
No. Please give me a line to add.
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026