Re: data integrity and inserts

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
> 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
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
>       joining column's datatypes do not match
>