Re: check constraint question

On 04/08/2014 03:31 PM, Rob Sargent wrote:
> On 04/08/2014 03:26 PM, CS_DBA wrote:
>>
>> On 04/08/2014 03:17 PM, Rob Sargent wrote:
>>> On 04/08/2014 03:09 PM, CS_DBA wrote:
>>>>
>>>> On 04/08/2014 02:58 PM, Rob Sargent wrote:
>>>>> On 04/08/2014 02:51 PM, CS_DBA wrote:
>>>>>> Hi All
>>>>>>
>>>>>> we have a table like so:
>>>>>>
>>>>>>
>>>>>> customer (
>>>>>> cust_id integer not null primary key,
>>>>>> cust_group_id integer not null,
>>>>>> group_account_id integer not null,
>>>>>> cust_name varchar not null,
>>>>>>
>>>>>> ...
>>>>>> )
>>>>>>
>>>>>> we want to force the cust_group_id to be unique across all
>>>>>> group_account_id's but not necessarily across the entire table
>>>>>>
>>>>>> I assume the best approach would be a check constraint yes? Will
>>>>>> this be excessively poor per performance if the table gets big?
>>>>>>
>>>>>> Thoughts?
>>>>>>
>>>>>> Thanks in advance
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> A unique index on cust_group_id and group_account_id doesn't do it
>>>>> for you?
>>>>>
>>>> oh right! duh! It's been one of those days....
>>>>
>>>>
>>>>
>>>>
>>>>
>>> Which column goes first depends on your lookup expectations.
>>
>> Thanks!
>>
>>
>> Here's another one:
>>
>>
>> customer (
>> cust_id integer not null primary key,
>> cust_group_id integer not null,
>> group_account_id integer not null,
>> cust_name varchar not null,
>> cust_template_id integer,
>> ...
>> )
>>
>> If cust_template_id IS NOT NULL then it must reference a valid cust_id
>>
>> Check constraint?
>>
>>
> Nope. Useless column :). You already have cust_id so cust_template_id
> is either null or already known.

Actually its a goofy design in the web app... users can enter the
template_id on the fly and if they do we want to enforce the fact that
it's a valid cust_id (meaning any existing cust_id can be used as a
template but made up template ID's - meaning an id that does not match
an existing cust_id should be disallowed)

Thoughts?