data integrity and inserts

CREATE UNIQUE INDEX uidx_thename ON names(the_name);

Should prevent duplicates.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002

Scott Frankel <leknarf@pacbell.net> 12/01/04 10:11 AM >>>

I want to ensure data integrity when inserting into a table, preventing
multiple
entries of identical rows of data.

Does this call for using a trigger?
How would triggers perform a query to test if data already exists in
the table?

(The doco outlines how triggers perform tests on NEW data inserted into
a
table; but I haven't found anything on data already extant.)

Thanks in advance!
Scott

sample table:

CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Scott Frankel wrote:

I want to ensure data integrity when inserting into a table, preventing
multiple
entries of identical rows of data.

Just use a unique index on the columns you want to make sure are not
duplicated.

Does this call for using a trigger?
How would triggers perform a query to test if data already exists in the
table?

(The doco outlines how triggers perform tests on NEW data inserted into a
table; but I haven't found anything on data already extant.)

Thanks in advance!
Scott

sample table:

CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

-- 
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

From: "Scott Frankel" <leknarf@pacbell.net>

I want to ensure data integrity when inserting into a table, preventing
multiple
entries of identical rows of data.

sample table:

CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);

sounds like a job for a UNIQUE constraint

gnari

1.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);
CREATE UNIQUE INDEX uidx_thename ON names(the_name);

vs.

2.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE);

Is the UNIQUE constraint in the second solution merely short-hand for
the explicit
index declaration of the first solution? Or is there a functional
difference between
them that I should choose between?

Thanks again!
Scott

On Dec 1, 2004, at 10:11 AM, Scott Frankel wrote:

On Wed, Dec 01, 2004 at 10:48:40 -0800,
Scott Frankel <leknarf@pacbell.net> wrote:

1.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);
CREATE UNIQUE INDEX uidx_thename ON names(the_name);

vs.

2.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE);

Is the UNIQUE constraint in the second solution merely short-hand for
the explicit
index declaration of the first solution? Or is there a functional
difference between
them that I should choose between?

Currently the only way to enforce a UNIQUE constraint is by using an index.
So there isn't really much difference between the two. However, I think
using the UNIQUE constraint provides better meaning than using an index
for people who might look at your definitions later.

The second is shorthand for the first. you get to choose the index name
in the first one.

Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002

Scott Frankel <leknarf@pacbell.net> 12/01/04 10:48 AM >>>

1.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);
CREATE UNIQUE INDEX uidx_thename ON names(the_name);

vs.

2.
CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text UNIQUE);

Is the UNIQUE constraint in the second solution merely short-hand for
the explicit
index declaration of the first solution? Or is there a functional
difference between
them that I should choose between?

Thanks again!
Scott

On Dec 1, 2004, at 10:11 AM, Scott Frankel wrote:

I want to ensure data integrity when inserting into a table,
preventing multiple
entries of identical rows of data.

Does this call for using a trigger?
How would triggers perform a query to test if data already exists in
the table?

(The doco outlines how triggers perform tests on NEW data inserted
into a
table; but I haven't found anything on data already extant.)

Thanks in advance!
Scott

sample table:

CREATE TABLE names (the_id SERIAL PRIMARY KEY, the_name text);

---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

"Ian Harding" <iharding@tpchd.org> writes:

The second is shorthand for the first. you get to choose the index name
in the first one.

IIRC you can force the index name in the second case too, by using
the fully unabbreviated CONSTRAINT syntax:

..., CONSTRAINT indexname UNIQUE(colname), ...

regards, tom lane

The first way also makes it possible to put the constraint on multiple
fields:

create unique index uidx_abc on my_table(col_a, col_b, col_c);

On Dec 2, 2004, at 1:51 PM, Ian Harding wrote:

On Thu, Dec 02, 2004 at 14:20:35 -0600,
Timothy Perrigo <tperrigo@wernervas.com> wrote:

The first way also makes it possible to put the constraint on multiple
fields:

create unique index uidx_abc on my_table(col_a, col_b, col_c);

You can do that with unique constraints as well.