On 04/08/2014 03:36 PM, CS_DBA wrote:
>
> 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?
>
>
Really goofy. They could type in any valid cust_id, theirs or not theirs.
What are you after with template_id. How would your app use it. Why
would user fill it in?