Array of foreign key

On 22 December 2017 20:02:43 CET, Thomas Poty <thomas.poty@gmail.com> wrote:

Hello all,

I use postgresql 9.5 and I am looking for way to have a column which is
an
array of a foreign key. If what I read is correct it is currently not
possible... Have you any idea how to implement this feature in a safe
way

Normalisation?

Can you explain that a bit more, what's the use-case?

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company

Actually, We are migrating from Mysql to postgresql and we have to replace
sets of Mysql. The migration we will be done in 2 steps: First, in a like
for like way. Second step will be using postgresql powerfulness

I think the most like for like way to migrate sets is using enum array.

Thank you Andreas.

Thomas

Le 22 déc. 2017 20:22, "Andreas Kretschmer" <andreas@a-kretschmer.de> a
écrit :

Show quoted text

On 22 December 2017 20:02:43 CET, Thomas Poty <thomas.poty@gmail.com>
wrote:

Hello all,

I use postgresql 9.5 and I am looking for way to have a column which is
an
array of a foreign key. If what I read is correct it is currently not
possible... Have you any idea how to implement this feature in a safe
way

Normalisation?

Can you explain that a bit more, what's the use-case?

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company

On Fri, Dec 22, 2017 at 3:40 PM, Thomas Poty <thomas.poty@gmail.com> wrote:

Actually, We are migrating from Mysql to postgresql and we have to replace
sets of Mysql. The migration we will be done in 2 steps: First, in a like
for like way. Second step will be using postgresql powerfulness

I think the most like for like way to migrate sets is using enum array.

Thank you Andreas.

Thomas

Le 22 déc. 2017 20:22, "Andreas Kretschmer" <andreas@a-kretschmer.de> a
écrit :

On 22 December 2017 20:02:43 CET, Thomas Poty <thomas.poty@gmail.com>
wrote:

Hello all,

I use postgresql 9.5 and I am looking for way to have a column which is
an
array of a foreign key. If what I read is correct it is currently not
possible... Have you any idea how to implement this feature in a safe
way

Normalisation?

Can you explain that a bit more, what's the use-case?

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company

Please DO NOT use EMUMs. That is old. They are hard to maintain. I also
know from experience that MySql does not check
integrity of enums. It's possible you can have data in a MySql table column
that is not valid for current enum constraint on that column.
EG: When porting, the enum for a columm (VALID) was "yes, no", but I found
a few that had "maybe" as data.

The way to go is to simply implement FOREIGN KEYs. Much easier to maintain
in PostgreSQL.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Please DO NOT use EMUMs. That is old. They are hard to maintain. I also
know from experience that MySql does not check
integrity of enums. It's possible you can have data in a MySql table
column
that is not valid for current enum constraint on that column.
EG: When porting, the enum for a columm (VALID) was "yes, no", but I
found
a few that had "maybe" as data.

The way to go is to simply implement FOREIGN KEYs. Much easier to
maintain
in PostgreSQL.

Full ack.

Andreas

--
2ndQuadrant - The PostgreSQL Support Company

On 2017-12-22 22:05:18 +0100, Andreas Kretschmer wrote:

Please DO NOT use EMUMs. That is old. They are hard to maintain. I
also know from experience that MySql does not check integrity of
enums. It's possible you can have data in a MySql table column that
is not valid for current enum constraint on that column. EG: When
porting, the enum for a columm (VALID) was "yes, no", but I found a
few that had "maybe" as data.

The way to go is to simply implement FOREIGN KEYs. Much easier to
maintain in PostgreSQL.

Full ack.

But that brings us back to the original question: Is there a way to
enforce foreign key constraints on the members of an array?

At insert time you can check with a trigger of course, and maybe there
is a way to do it in a check constraint. But that doesn't prevent you
from removing a row from the target table.

The only way I can think of is with an intersection table.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

On Sat, Dec 23, 2017 at 12:09 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

On 2017-12-22 22:05:18 +0100, Andreas Kretschmer wrote:

Please DO NOT use EMUMs. That is old. They are hard to maintain. I
also know from experience that MySql does not check integrity of
enums. It's possible you can have data in a MySql table column that
is not valid for current enum constraint on that column. EG: When
porting, the enum for a columm (VALID) was "yes, no", but I found a
few that had "maybe" as data.

The way to go is to simply implement FOREIGN KEYs. Much easier to
maintain in PostgreSQL.

Full ack.

But that brings us back to the original question: Is there a way to
enforce foreign key constraints on the members of an array?

At insert time you can check with a trigger of course, and maybe there
is a way to do it in a check constraint. But that doesn't prevent you
from removing a row from the target table.

The only way I can think of is with an intersection table.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

...Is there a way to
enforce foreign key constraints on the members of an array?
At insert time you can check with a trigger of course, and maybe there
is a way to do it in a check constraint

I don't think you understand how Foreign Key constraints work in PostgreSQL.
PostgreSQL will prevent any insert where the value of a column is not
within the FK table.
So you DO NOT need a check constraint or trigger.

What I do not understand is your reference to a FK "array".
So for the sake of CLARIDICATION, would you please
1, State the version of PostgreSQL
2. State the O/S
3. Provide an example of an FK "array" that you are concerned with.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

I don't think you understand how Foreign Key constraints work in
PostgreSQL.
PostgreSQL will prevent any insert where the value of a column is not
within the FK table.
So you DO NOT need a check constraint or trigger.

I thought the OP was asking for this feature ("Support for Array ELEMENT
Foreign Keys"):

/messages/by-id/1343842863.5162.4.camel@greygoo.devise-it.lan

which would be super-handy, but doesn't actually exist. You can enforce it
yourself with a trigger on both tables, but I still hope this someday gets
included into Postgres!

Ken

On Sat, Dec 23, 2017 at 12:49 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:

I don't think you understand how Foreign Key constraints work in
PostgreSQL.
PostgreSQL will prevent any insert where the value of a column is not
within the FK table.
So you DO NOT need a check constraint or trigger.

I thought the OP was asking for this feature ("Support for Array ELEMENT
Foreign Keys"):

/messages/by-id/1343842863.
5162.4.camel@greygoo.devise-it.lan#1343842863.5162.4.
camel@greygoo.devise-it.lan

which would be super-handy, but doesn't actually exist. You can enforce
it yourself with a trigger on both tables, but I still hope this someday
gets included into Postgres!

Ken

Actually, I think the op may be referring to a MULTI COLUMN FK array
EG:

DROP TABLE drivers
CREATE TABLE drivers (
driver_id integer PRIMARY KEY,
driver_first_name text,
driver_last_name text,
CONSTRAINT drivers_uq UNIQUE (driver_first_name, driver_last_name)
);

CREATE TABLE races (
race_id integer PRIMARY KEY,
title text,
race_day DATE,
driver_first_name text,
driver_last_name text,
final_positions integer,
CONSTRAINT races_driver_fk FOREIGN KEY (driver_first_name,
driver_last_name)
REFERENCES drivers (driver_first_name, driver_last_name)
);

and that is available,
which is why I requested clarification.

So I guess we will have to wait for the op's response.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 2017-12-23 12:24:07 -0500, Melvin Davidson wrote:

On Sat, Dec 23, 2017 at 12:09 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

...Is there a way to
enforce foreign key constraints on the members of an array?
At insert time you can check with a trigger of course, and maybe there
is a way to do it in a check constraint

I don't think you understand how Foreign Key constraints work in PostgreSQL.
PostgreSQL will prevent any insert where the value of a column is not within
the FK table.
So you DO NOT need a check constraint or trigger.

What I do not understand is your reference to a FK "array".

If you do not understand something, please ask. Don't claim that other
people "don't understand how X works" just because you don't know what
they are talking about. Also, please pay a bit of attention who you
are replying to. I am not the OP. I just understand what he wants (or at
least I think I do).

So for the sake of CLARIDICATION, would you please
1, State the version of PostgreSQL
2. State the O/S

Why should I? You didn't state the OS and PostgreSQL version you use
either. And I don't think you should, as it is irrelevant for the
discussion.

3. Provide an example of an FK "array" that you are concerned with.

I think the OP wants something like this:

create table features (
id serial primary key,
name varchar not null
);

create table products (
id serial primary key,
name varchar not null,
array_of_features int[]
references [] features(id) -- XXX - invented syntax
);

where products.array_of_features can only contain integers from
features.id. This doesn't currently (as of 10.1) work.
As I wrote, you could use triggers, but there is no way to declare that
foreign key directly.

If you want foreign keys, you have to use an intersection table:

create table features (
id serial primary key,
name varchar not null
);

create table products (
id serial primary key,
name varchar not null
);

create table product_features (
product references products(id),
feature references features(id)
);

But that has a relatively high overhead both in storage and for queries.

I can understand why the OP wants that. I could have used something like
this in the past, too.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

On 2017-12-23 13:14:50 -0500, Melvin Davidson wrote:

On Sat, Dec 23, 2017 at 12:49 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:

I thought the OP was asking for this feature ("Support for Array ELEMENT
Foreign Keys"):

/messages/by-id/1343842863.
5162.4.camel@greygoo.devise-it.lan#1343842863.5162.4.
camel@greygoo.devise-it.lan 

which would be super-handy, but doesn't actually exist.  You can enforce it
yourself with a trigger on both tables, but I still hope this someday gets
included into Postgres!

Actually, I think the op may be referring to a MULTI COLUMN FK array

I see no reason to assume that. He explicitely asked about an array of
foreign keys, and there is no array here.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

On Sat, Dec 23, 2017 at 2:27 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

On 2017-12-23 13:14:50 -0500, Melvin Davidson wrote:

On Sat, Dec 23, 2017 at 12:49 PM, Ken Tanzer <ken.tanzer@gmail.com>

wrote:

I thought the OP was asking for this feature ("Support for Array

ELEMENT

Foreign Keys"):

/messages/by-id/1343842863.
5162.4.camel@greygoo.devise-it.lan#1343842863.5162.4.
camel@greygoo.devise-it.lan

which would be super-handy, but doesn't actually exist. You can

enforce it

yourself with a trigger on both tables, but I still hope this

someday gets

included into Postgres!

Actually, I think the op may be referring to a MULTI COLUMN FK array

I see no reason to assume that. He explicitely asked about an array of
foreign keys, and there is no array here.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

Peter,

If you do not understand something, please ask.

Your response is inappropriate and offensive. I specifically asked for
clarification
because the op stated "column which is an array of a foreign key" but gave
no details.

Don't claim that other people "don't understand how X works"

Again, from the op's reference to using constraint's and triggers, it
seemed
like the op did not fully understand the FK's would do the same.

Also, it is not necessary to state which version I PostgreSQL I use,
because that is irrelevant.
I am simply trying to help the op.

In the future, please refrain from chastising those of us that are trying
to help others.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Good evening all,
I have just see all the messages. Thanks for that.

First, Peter Holzer has exactly understooden my need.
I am à bit disappointed this feature is not implemented. It would be great.

Then, I know enum is probably not the best choice but it is historic in
our database. Like I said previously, We are working on the first of two
steps migration and we will probably not use them after de second phase of
migration. You also have to know make this changes needs a lot of tests to
be sure the results will be correct and expected.! For our company it is
not so easy...

After that, we are running on pgsql 9.5 and centos 7.x.

Finally, keep in mind we are here to help each others.

Thanks for your help

Thomas

Le 23 déc. 2017 20:25, "Peter J. Holzer" <hjp-pgsql@hjp.at> a écrit :

On 2017-12-23 12:24:07 -0500, Melvin Davidson wrote:

On Sat, Dec 23, 2017 at 12:09 PM, Peter J. Holzer <hjp-pgsql@hjp.at>

wrote:

...Is there a way to
enforce foreign key constraints on the members of an array?
At insert time you can check with a trigger of course, and maybe there
is a way to do it in a check constraint

I don't think you understand how Foreign Key constraints work in

PostgreSQL.

PostgreSQL will prevent any insert where the value of a column is not

within

the FK table.
So you DO NOT need a check constraint or trigger.

What I do not understand is your reference to a FK "array".

If you do not understand something, please ask. Don't claim that other
people "don't understand how X works" just because you don't know what
they are talking about. Also, please pay a bit of attention who you
are replying to. I am not the OP. I just understand what he wants (or at
least I think I do).

So for the sake of CLARIDICATION, would you please
1, State the version of PostgreSQL
2. State the O/S

Why should I? You didn't state the OS and PostgreSQL version you use
either. And I don't think you should, as it is irrelevant for the
discussion.

3. Provide an example of an FK "array" that you are concerned with.

I think the OP wants something like this:

create table features (
id serial primary key,
name varchar not null
);

create table products (
id serial primary key,
name varchar not null,
array_of_features int[]
references [] features(id) -- XXX - invented syntax
);

where products.array_of_features can only contain integers from
features.id. This doesn't currently (as of 10.1) work.
As I wrote, you could use triggers, but there is no way to declare that
foreign key directly.

If you want foreign keys, you have to use an intersection table:

create table features (
id serial primary key,
name varchar not null
);

create table products (
id serial primary key,
name varchar not null
);

create table product_features (
product references products(id),
feature references features(id)
);

But that has a relatively high overhead both in storage and for queries.

I can understand why the OP wants that. I could have used something like
this in the past, too.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

On 23 Dec 2017 9:54 pm, "Thomas Poty" <thomas.poty@gmail.com> wrote:

Good evening all,
I have just see all the messages. Thanks for that.

First, Peter Holzer has exactly understooden my need.
I am à bit disappointed this feature is not implemented. It would be great.

Then, I know enum is probably not the best choice but it is historic in
our database. Like I said previously, We are working on the first of two
steps migration and we will probably not use them after de second phase of
migration. You also have to know make this changes needs a lot of tests to
be sure the results will be correct and expected.! For our company it is
not so easy...

After that, we are running on pgsql 9.5 and centos 7.x.

Finally, keep in mind we are here to help each others.

Thanks for your help

Thomas

Le 23 déc. 2017 20:25, "Peter J. Holzer" <hjp-pgsql@hjp.at> a écrit :

On 2017-12-23 12:24:07 -0500, Melvin Davidson wrote:

On Sat, Dec 23, 2017 at 12:09 PM, Peter J. Holzer <hjp-pgsql@hjp.at>

wrote:

...Is there a way to
enforce foreign key constraints on the members of an array?
At insert time you can check with a trigger of course, and maybe there
is a way to do it in a check constraint

I don't think you understand how Foreign Key constraints work in

PostgreSQL.

PostgreSQL will prevent any insert where the value of a column is not

within

the FK table.
So you DO NOT need a check constraint or trigger.

What I do not understand is your reference to a FK "array".

If you do not understand something, please ask. Don't claim that other
people "don't understand how X works" just because you don't know what
they are talking about. Also, please pay a bit of attention who you
are replying to. I am not the OP. I just understand what he wants (or at
least I think I do).

So for the sake of CLARIDICATION, would you please
1, State the version of PostgreSQL
2. State the O/S

Why should I? You didn't state the OS and PostgreSQL version you use
either. And I don't think you should, as it is irrelevant for the
discussion.

3. Provide an example of an FK "array" that you are concerned with.

I think the OP wants something like this:

create table features (
id serial primary key,
name varchar not null
);

create table products (
id serial primary key,
name varchar not null,
array_of_features int[]
references [] features(id) -- XXX - invented syntax
);

where products.array_of_features can only contain integers from
features.id. This doesn't currently (as of 10.1) work.
As I wrote, you could use triggers, but there is no way to declare that
foreign key directly.

If you want foreign keys, you have to use an intersection table:

create table features (
id serial primary key,
name varchar not null
);

create table products (
id serial primary key,
name varchar not null
);

create table product_features (
product references products(id),
feature references features(id)
);

But that has a relatively high overhead both in storage and for queries.

I can understand why the OP wants that. I could have used something like
this in the past, too.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

This is an interesting feature you are talking about. However, I'm not sure
I properly understand the use-case(s). Could you perhaps please give some
examples to help me understand? (I don't just mean some SQL code, I mean an
explanation of the business use-cases served by the code).

Kind regards,
Daevor, The Devoted

On Sun, Dec 24, 2017 at 1:11 AM, Daevor The Devoted <dollien@gmail.com>
wrote:

Le 23 déc. 2017 20:25, "Peter J. Holzer" <hjp-pgsql@hjp.at> a écrit :

I think the OP wants something like this:

create table features (
id serial primary key,
name varchar not null
);

create table products (
id serial primary key,
name varchar not null,
array_of_features int[]
references [] features(id) -- XXX - invented syntax
);

where products.array_of_features can only contain integers from
features.id. This doesn't currently (as of 10.1) work.
As I wrote, you could use triggers, but there is no way to declare that
foreign key directly.

This is an interesting feature you are talking about. However, I'm not
sure I properly understand the use-case(s). Could you perhaps please give
some examples to help me understand? (I don't just mean some SQL code, I
mean an explanation of the business use-cases served by the code).

Other than Peter's simple example above, you could implement a simple RBAC
system this way. As with the features example above, you would have a
table of roles that are referenced by a user table. In this case, you
would need an int array with foreign key constraints on each element in
the array to the id column in the role table.

On 2017-12-23 14:40:13 -0500, Melvin Davidson wrote:

On Sat, Dec 23, 2017 at 2:27 PM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

If you do not understand something, please ask.

Your response is inappropriate and offensive.

I apologize for my tone. I should have calmed down before replying.

I do not apologize for the substance, though. I think it is entirely
appropriate to point out inappropriate and offensive behaviour, and I
think it is more helpful to point out the specific aspects that one
finds inappropriate and offensive than just making a blanket claim.

To repeat:

* I think it is offensive to claim that somebody doesn't understand some
basic aspect of RDBMSs unless that person has written something which
is clearly false. This is a list about an RDBMS and one should assume
that most of the people here (or at least the regular contributors)
know at least the basics. Since you were apparently aware that you
didn't understand what I was writing about, it would have been
appropriate to consider that you might have misunderstood whatever you
thought I had written about foreign keys.

* I think it is impolite not to take the 2 seconds to check who you are
replying to. I understand that in a long thread it is easy to lose
track of who started it and who wrote what, but this thread wasn't
that long.

* I think it is inappropriate to ask some random participant in the
middle of a discussion what OS and PostgreSQL version they are
running, unless they have related some specific observation about
their system. Even if I had not been a random participant, but the OP,
I would have considered the question inappropriate, since at that
point in the discussion it should have been clear that none of the
topics (arrays, enums, foreign keys, ...) is dependent on the exact
PostgreSQL version, much less the OS.

I specifically asked for clarification because the op stated "column
which is an array of a foreign key" but gave no details.

Don't claim that other people "don't understand how X works"

Again, from the op's reference to using constraint's and triggers, it seemed
like the op did not fully understand the FK's would do the same.

AFAICS the OP didn't mention triggers at all. Ken and I mentioned them
as a way to simulate what the OP wanted.

Also, it is not necessary to state which version I PostgreSQL I use, because
that is irrelevant.

It is also irrelevant what version of PostgreSQL I use (or what version
Ken or Andreas use). The version the OP uses might have been mildly
interesting at the start of the discussion.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/&gt;

On Sat, Dec 23, 2017 at 11:11 PM, Daevor The Devoted <dollien@gmail.com>
wrote:

This is an interesting feature you are talking about. However, I'm not
sure I properly understand the use-case(s). Could you perhaps please give
some examples to help me understand? (I don't just mean some SQL code, I
mean an explanation of the business use-cases served by the code).

I work on a data system for nonprofit organizations that uses Postgresql.

We're constantly having to collect new data, adding new tables or new
columns to existing tables. The acceptable values are all store in tables
and referenced as foreign keys. This works great for situations where
there is one possible answer (e.g., "What is your marital status?") But
for questions which allow multiple responses (e.g., "In the last 30 days,
which types of medical care did you utilize?"), we use an array to store
all the values.

Of course, it is possible to create a separate table to store each
individual response, and tie those back to the table. But that is
considerably more cumbersome, for in my case not much benefit. (We also
have a UI that automatically picks up the FKs to generate web forms with
the allowable responses.) So in my case, allowing the array elements to be
enforced by FKs would be of considerable benefit. In practice, I have
ended up using the arrays anyway, and sacrificing the greater data
integrity the FKs would provide. I've left them commented out in my code
though, wistfully waiting for the day I can turn them on. :)

I've included an example of one of these tables below. Hope this helps
answer your question, even if it may be unique to my particular situation.

Cheers,
Ken

CREATE TABLE tbl_veteran_status (
veteran_status_id SERIAL PRIMARY KEY,
client_id INTEGER NOT NULL REFERENCES tbl_client ( client_id ),
veteran_status_date DATE NOT NULL,
year_entered INTEGER NOT NULL CHECK (year_entered BETWEEN
1920 AND date_part('year',current_date)),
year_exited INTEGER CHECK (year_exited BETWEEN 1920 AND
date_part('year',current_date)),* military_theatre_codes
VARCHAR(10)[], --REFERENCES tbl_l_military_theatre
(military_theatre_code),
military_branch_codes VARCHAR(10)[], --REEFERENCES
tbl_l_military_branch (military_branch_code),
* veteran_status_code VARCHAR(10) NOT NULL REFERENCES
tbl_l_veteran_status ( veteran_status_code ),
has_va_benefits BOOLEAN NOT NULL,
has_service_disability BOOLEAN NOT NULL,
has_military_pension BOOLEAN NOT NULL,
has_received_va_hospital_care BOOLEAN NOT NULL,
added_by INTEGER NOT NULL REFERENCES tbl_staff (staff_id),
added_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
changed_by INTEGER NOT NULL REFERENCES tbl_staff (staff_id),
changed_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
deleted_at TIMESTAMP(0),
deleted_by INTEGER REFERENCES tbl_staff(staff_id),
deleted_comment TEXT,
sys_log TEXT

CONSTRAINT non_conflicting_data CHECK (
(veteran_status_code IN ('0','5') AND has_va_benefits IS FALSE
AND has_service_disability IS FALSE
AND has_military_pension IS FALSE AND
has_received_va_hospital_care IS FALSE)
OR veteran_status_code NOT IN ('0','5')
)
);

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.