Re: [GENERAL] data integrity and inserts

2004-12-02 Thread Bruno Wolff III
On Thu, Dec 02, 2004 at 14:20:35 -0600, Timothy Perrigo <[EMAIL PROTECTED]> 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. ---

Re: [GENERAL] data integrity and inserts

2004-12-02 Thread Timothy Perrigo
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: The second is shorthand for the first. you get to choose the index name in the first one. Ian Harding Pro

Re: [GENERAL] data integrity and inserts

2004-12-02 Thread Tom Lane
"Ian Harding" <[EMAIL PROTECTED]> 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)

Re: [GENERAL] data integrity and inserts

2004-12-02 Thread Ian Harding
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 [EMAIL PROTECTED] Phone: (253) 798-3549 Pager: (253) 754-0002 >>> Scott Frankel <[EMAIL PROTECTED]> 12/01/04 10:48 AM >>> 1. CREATE

Re: [GENERAL] data integrity and inserts

2004-12-01 Thread Bruno Wolff III
On Wed, Dec 01, 2004 at 10:48:40 -0800, Scott Frankel <[EMAIL PROTECTED]> 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 UN

Re: [GENERAL] data integrity and inserts

2004-12-01 Thread Scott Frankel
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 decla

Re: [GENERAL] data integrity and inserts

2004-12-01 Thread Ian Harding
CREATE UNIQUE INDEX uidx_thename ON names(the_name); Should prevent duplicates. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department [EMAIL PROTECTED] Phone: (253) 798-3549 Pager: (253) 754-0002 >>> Scott Frankel <[EMAIL PROTECTED]> 12/01/04 10:11 AM >>> I want to ensure dat

Re: [GENERAL] data integrity and inserts

2004-12-01 Thread Joshua D. Drake
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

Re: [GENERAL] data integrity and inserts

2004-12-01 Thread gnari
From: "Scott Frankel" <[EMAIL PROTECTED]> > > 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 constrain

[GENERAL] data integrity and inserts

2004-12-01 Thread Scott Frankel
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 inser