Re: check constraint question

On 04/08/2014 04:08 PM, Rob Sargent wrote:
> On 04/08/2014 03:53 PM, CS_DBA wrote:
>>
>> On 04/08/2014 03:41 PM, Rob Sargent wrote:
>>> 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?
>>>
>> Not sure yet (new client)... for now they simply want to force the
>> template column to be a valid cust_id, if it is not null... later
>> I'll be digging into their design and pushing them to make some db
>> architecture changes...
>>
>>
> So randomly set it to the cust_id :). I am not sure if a column and
> REFERENCE a column in same table. That you'll just have to lookup or
> try. But you probably need a check: is null or equals cust_id so they
> cannot randomly guess another cust_id. Are you sure this field
> shouldn't reference some as yet undefined template table?

I'll verify tomorrow... thx