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.