check constraint question

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?

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....

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.

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?

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.

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?

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?

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...

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?

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

Based on your first question a customer id itself is not a valid designator;
you have to specify (or link) in the group as well.

Not tested but should work:

FOREIGN KEY (template, group) REFERENCES customer (cust_id, group)

Depends on whether you want to allow cross-group associations if you need a
separate group template id.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/check-constraint-question-tp5799252p5799282.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, 08 Apr 2014 15:53:48 -0600
CS_DBA <cs_dba@consistentstate.com> wrote:

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...

It seems to be a different version of the textbook exercice involving
EMPLOYEE_ID and MANAGER_ID.

--
Alberto Cabello Sánchez
<alberto@unex.es>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general