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?