Menu
▾
▴
sql-ledger-users
|
From: alta <al...@al...> - 2001-12-18 22:37:19
|
I sometimes get duplicate records, for example when creating a
customer. I know this should not happen, but it does.
To eliminate this problem, I have tried to add a PRIMARY KEY with
psql. Here's how:
alter table customer add constraint cs1 primary key(id);
But, I get this error:
"ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented for that
constraint type."
According to documentation (PostgreSQL Essential Reference), the ADD
CONSTRAINT command is supported.
Other than saving data, deleting the table, re-creating the table
with the primary key, and restoring data, is there an easy to add a
primary key (that works)?
... Reed
--
Reed White - ALTA RESEARCH - www.alta-research.com
Phone: 877-360-2582 - Email: al...@al...
|
|
From: Thomas G. <to...@ad...> - 2001-12-19 00:23:21
|
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! */
--------------------------------------------------------------------
|
|
From: alta <al...@al...> - 2001-12-19 01:15:50
|
Thanks Thomas ... However, your suggestion produced an error. psql was run from user postgres, version 7.1.3-11, with the following results: =# ALTER TABLE customer ADD CONSTRAINT cs1 UNIQUE (id); ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented for that constraint type ... Reed |
|
From: Roderick A. A. <raa...@ti...> - 2001-12-19 05:07:02
|
On Tue, 18 Dec 2001, alta wrote:
>
> Thanks Thomas ...
>
> However, your suggestion produced an error. psql was run from user
> postgres, version 7.1.3-11, with the following results:
>
> =# ALTER TABLE customer ADD CONSTRAINT cs1 UNIQUE (id);
>
> ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented for that
> constraint type
>
From the PostgreSQL man page on alter_table
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)]).
Try
CREATE UNIQUE INDEX cs1_pk ON customer (id);
Not sure if Dieter's code will trap the error returned if a duplicate ID
is inserted or updated.
Cheers,
Rod
--
Let Accuracy Triumph Over Victory
Zetetic Institute
"David's Sling"
Marc Stiegler
|
|
From: Thomas G. <to...@ad...> - 2001-12-19 13:23:22
|
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! */
--------------------------------------------------------------------
|
Thanks for helping keep SourceForge clean.
X