Re: Adding PostgreSQL PRIMARY KEY - help

[Thomas G. <to...@ad...>]

On Tue, 18 Dec 2001, alta wrote:

>=20
> I sometimes get duplicate records, for example when creating a=20
> customer.  I know this should not happen, but it does.
>=20
> To eliminate this problem, I have tried to add a PRIMARY KEY with=20
> psql.  Here's how:
>=20
>     alter table customer add constraint cs1 primary key(id);
>=20
> But, I get this error:
>=20
> "ERROR:  ALTER TABLE / ADD CONSTRAINT is not implemented for that=20
> constraint type."
>=20
> According to documentation (PostgreSQL Essential Reference), the ADD=20
> CONSTRAINT command is supported.

Make it unique:

ALTER TABLE customer ADD CONSTRAINT cs1 UNIQUE (id);

This creates an implicit index named cs1.
Because there is overhead (disk space) with indexes you might get
this operation to do double duty by choosing a field you'd like
to index anyway --- to speed performance.

To block dupes I sometimes make composite indexes:

CREATE UNIQUE INDEX no_dupes ON (patient_id, dept_id, track_id);
This gives me an index **** on the fields that I include in every query ***
anyway and blocks silly user tricks at the same time.

Good luck!  You might want to get a copy of SQL In A Nutshell, published
by O'Reilly.  Great postgres/oracle reference.  Concise but complete.

--------------------------------------------------------------------
              Saint Vincent Catholic Medical Centers               =20
--------------------------------------------------------------------
Thomas Good                          tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst                              Phone:  718-818-5528=20
Behavioral Health Services                      Fax:    718-818-5056 =20
Residential Services                            Mobile: 917-282-7359 =20
--------------------------------------------------------------------
/*         Rekordmeister ist nur der FC Bayern M=FCnchen!           */
--------------------------------------------------------------------