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
|
