Thread: Re: [SQL] database design SQL prob.

Re: [SQL] database design SQL prob.

From
Stuart Rison
Date:
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              |
+--------------------------+--------------------------------------+


Re: [SQL] database design SQL prob.

From
"D'Arcy" "J.M." Cain
Date:
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.


Re: [SQL] database design SQL prob.

From
Herouth Maoz
Date:
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




Re: [SQL] database design SQL prob.

From
Don Yury
Date:
"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


Re: [SQL] database design SQL prob.

From
"D'Arcy" "J.M." Cain
Date:
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.


Re: [SQL] database design SQL prob.

From
Bruce Momjian
Date:
> 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