Re: unique amount more than one table

On Apr 5, 2011, at 5:50 PM, Rob Sargent wrote:

>
>
> On 04/05/2011 04:02 PM, Perry Smith wrote:
>> I have five tables each with a "name" field. Due to limitations in my user interface, I want a name to be unique amoung these five tables.
>>
>> I thought I could first create a view with something like:
>>
>> SELECT name, 'table1' as type from table1
>> UNION ALL
<snip>
>> f
>> (1 row)
>>
>> but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road?
>>
>> Thank you for your time,
>> pedz
>>
>>
>
> You might try making a separate name table and having a unique index there and make the other users of name refer to the new table's name field. (I would stick on id on the new name table...)

Thanks to all.

I think this is the way I'm going to go. I'll have an id, name, and type to tell me which of the other tables owns it. Most of the other tables don't need to exist even.

Thank you again,
pedz