Search Postgresql Archives

Re: check constraint question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux