Re: Constraint + where

2017-03-20 13:27 GMT+13:00 Melvin Davidson <melvin6925(at)gmail(dot)com>:

>
>
> On Sun, Mar 19, 2017 at 8:16 PM, Patrick B <patrickbakerbr(at)gmail(dot)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)
> );