Constraint + where

On Sun, Mar 19, 2017 at 8:16 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

Hi guys,

I've got a column 'type_note' on a new table that it's being designed:

type_note varchar(32) NOT NULL;

On that column, there will be three different data:

1. yes
2. no
3. maybe

I wanna create a FK but just when the data on that column is = maybe.

How can I do that? Thanks!

Why just "maybe"? Since there can only be three valid answers, why not FK
for all three?

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

2017-03-20 13:27 GMT+13:00 Melvin Davidson <melvin6925@gmail.com>:

On Sun, Mar 19, 2017 at 8:16 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:

Hi guys,

I've got a column 'type_note' on a new table that it's being designed:

type_note varchar(32) NOT NULL;

On that column, there will be three different data:

1. yes
2. no
3. maybe

I wanna create a FK but just when the data on that column is = maybe.

How can I do that? Thanks!

Why just "maybe"? Since there can only be three valid answers, why not FK
for all three?

--

I was able to get what I needed this way:

create table testing_fk_conditional_1 (

On Sun, Mar 19, 2017 at 8:27 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

On Sun, Mar 19, 2017 at 8:16 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:

Hi guys,

I've got a column 'type_note' on a new table that it's being designed:

type_note varchar(32) NOT NULL;

On that column, there will be three different data:

1. yes
2. no
3. maybe

I wanna create a FK but just when the data on that column is = maybe.

How can I do that? Thanks!

Why just "maybe"? Since there can only be three valid answers, why not FK
for all three?

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

Also "type_note varchar(32) NOT NULL;" is pointless.
You should make it either type_note(1) with values of Y,N or M.

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

On Sun, Mar 19, 2017 at 8:53 PM, Patrick B <patrickbakerbr@gmail.com> wrote:

2017-03-20 13:27 GMT+13:00 Melvin Davidson <melvin6925@gmail.com>:

On Sun, Mar 19, 2017 at 8:16 PM, Patrick B <patrickbakerbr@gmail.com>
wrote:

Hi guys,

I've got a column 'type_note' on a new table that it's being designed:

type_note varchar(32) NOT NULL;

On that column, there will be three different data:

1. yes
2. no
3. maybe

I wanna create a FK but just when the data on that column is = maybe.

How can I do that? Thanks!

Why just "maybe"? Since there can only be three valid answers, why not FK
for all three?

--

I was able to get what I needed this way:

create table testing_fk_conditional_1 (

id serial NOT NULL PRIMARY KEY,
account_id bigint,
user_id bigint,
type_note integer NOT NULL,
CHECK (type_note = 100 AND user_id IS NOT NULL OR type_note = 200 AND
account_id IS NOT NULL)
);

That is not what you asked for originally, but as long as you are happy.

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