Re: Adding PostgreSQL PRIMARY KEY - help

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

On Tue, 18 Dec 2001, Roderick A. Anderson wrote:

> On Tue, 18 Dec 2001, alta wrote:
>=20
> >=20
> > Thanks Thomas ...
> >=20
> > However, your suggestion produced an error.  psql was run from user=20
> > postgres, version 7.1.3-11, with the following results:
> >=20
> > =3D# ALTER TABLE customer ADD CONSTRAINT cs1 UNIQUE (id);
> >=20
> > ERROR:  ALTER TABLE / ADD CONSTRAINT is not implemented for that=20
> > constraint type
> >=20
>=20
> >From the PostgreSQL man page on alter_table
>=20
>        In  the  current  implementation,  only  FOREIGN  KEY con
>        straints can be added to a table. To create  or  remove  a
>        unique constraint, create a unique index (see CREATE INDEX
>        [create_index(l)]).


Yeah, sorry!  I use 7.2 ...

> =09CREATE UNIQUE INDEX cs1_pk ON customer (id);

I suspect this is what really happens when we ADD CONSTRAINT anyway
(in 7.2beta).  I shifted to 7.2 as I was looking for DROP COLUMN, but
it still hasn't been unveiled.  :-(

> Not sure if Dieter's code will trap the error returned if a duplicate ID
> is inserted or updated.

All of Dieter's stuff has a method called 'dberror' invoked at the
end of each dbh->do() or sth->execute() call (perl DBI methods to exec=20
queries...)

If you look through the src you'll see the strings like:
$dbh->do($query) || $form->dberror($query);
This 'dberror' method call is declared in Form.pm (in the SL=20
directory) - for the curious.  It dumps the std $DBI::errstr
(which usually includes the complaint from the backend:
'cannot insert a duplicate key...') as well as a variable called
$msg which is most likely the failed query string.=20

--------------------------------------------------------------------
              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!           */
--------------------------------------------------------------------