Thread: information_schema.check_constraints Inconsistencies

Hello,

I am writing with regards to some wrong results I keep on receiving when using check_constraints view from information_schema:

(1) First, it shows constraints grouped by name, regardless of the relation used: when having two constraints with the same name in different tables, it shows both in both tables, regardless of their belonging;

(2) Second, it also lists NOT NULL constraints, even though they are not created as check constraints.

See, for example, the following select:

        select *
          from information_schema.table_constraints c
          join information_schema.check_constraints ck
            on c.constraint_name = ck.constraint_name
           and c.constraint_schema = ck.constraint_schema
           and c.constraint_catalog = ck.constraint_catalog
         where c.constraint_type = 'CHECK'
           and c.table_schema = 'events'
           and c.table_name = 'horse_racing_purchase_event'

It returns data in --PASTE1-- (at end).

Postgresql version used:

PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit

create table if not exists events.event
(
    id bigserial not null
        constraint event_pkey
            primary key,
    occurred_at timestamp default CURRENT_TIMESTAMP not null,
    operator_id integer not null,
    player_id integer not null,
    player_ip inet not null,
    session_id uuid not null,
    has_player_confirmation boolean default true not null,
    transformed_event text,
    arjel_type_id smallint
)
;

create table if not exists events.horse_racing_purchase_event
(
    purchase_id bigint not null,
    event_label_as_shown_to_player text not null
        constraint c_event_label_as_shown_to_player
            check (public.is_valid_string(256, event_label_as_shown_to_player)),
    balance_before money,
    balance_after money,
    bet_amount money,
    bet_contribution_from_operator money,
    bonus_before money,
    bonus_after money,
    bonus_amount money,
    bonus_name text,
    race_type text not null
        constraint c_race_type
            check (public.is_valid_string(64, race_type)),
    bet_name_as_shown_to_player text not null
        constraint c_bet_name_as_shown_to_player
            check (public.is_valid_string(64, bet_name_as_shown_to_player)),
    event_date timestamp not null,
    constraint horse_racing_purchase_event_pkey
        primary key (id),
    constraint c_balance
        check ((((balance_before + balance_after) + bet_amount) IS NOT NULL) OR (COALESCE(balance_before, balance_after, bet_amount) IS NULL)),
    constraint c_bonus
        check ((((bonus_before + bonus_after) + bonus_amount) IS NOT NULL) OR (COALESCE(bonus_before, bonus_after, bonus_amount) IS NULL))
)
inherits (events.event)
;

comment on column events.horse_racing_purchase_event.bet_amount is 'without operator contribution and bonuses'
;

create table if not exists events.balance_adjustment_event
(
    account_type_id smallint not null
        constraint balance_adjustment_event_account_type_id_fkey
            references agreement_type,
    information_text text not null,
    adjustment_type_id smallint not null
        constraint balance_adjustment_event_adjustment_type_id_fkey
            references adjustment_type,
    balance_before numeric(21,4),
    balance_after numeric(21,4),
    adjustment_to_balance_amount numeric(21,4),
    bonus_before numeric(21,4),
    bonus_after numeric(21,4),
    adjustment_to_bonus_amount numeric(21,4),
    constraint balance_adjustment_event_pkey
        primary key (id),
    constraint c_bonus
        check ((((adjustment_to_bonus_amount + bonus_before) + bonus_after) IS NOT NULL) OR (COALESCE(adjustment_to_bonus_amount, bonus_before, bonus_after) IS NULL)),
    constraint c_balance
        check ((((adjustment_to_balance_amount + balance_before) + balance_after) IS NOT NULL) OR (COALESCE(adjustment_to_balance_amount, balance_before, balance_after) IS NULL))
)
inherits (events.event)
;

--PASTE1--

i_r_french_regulation    events    c_balance    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    c_balance    (((((balance_before + balance_after) + bet_amount) IS NOT NULL) OR (COALESCE(balance_before, balance_after, bet_amount) IS NULL)))
i_r_french_regulation    events    c_balance    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    c_balance    (((((adjustment_to_balance_amount + balance_before) + balance_after) IS NOT NULL) OR (COALESCE(adjustment_to_balance_amount, balance_before, balance_after) IS NULL)))
i_r_french_regulation    events    c_bet_name_as_shown_to_player    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    c_bet_name_as_shown_to_player    (is_valid_string(64, bet_name_as_shown_to_player))
i_r_french_regulation    events    c_bonus    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    c_bonus    (((((adjustment_to_bonus_amount + bonus_before) + bonus_after) IS NOT NULL) OR (COALESCE(adjustment_to_bonus_amount, bonus_before, bonus_after) IS NULL)))
i_r_french_regulation    events    c_bonus    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    c_bonus    (((((bonus_before + bonus_after) + bonus_amount) IS NOT NULL) OR (COALESCE(bonus_before, bonus_after, bonus_amount) IS NULL)))
i_r_french_regulation    events    c_event_label_as_shown_to_player    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    c_event_label_as_shown_to_player    (is_valid_string(256, event_label_as_shown_to_player))
i_r_french_regulation    events    c_event_label_as_shown_to_player    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    c_event_label_as_shown_to_player    (((event_label_as_shown_to_player IS NULL) OR is_valid_string(256, event_label_as_shown_to_player)))
i_r_french_regulation    events    c_race_type    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    c_race_type    (is_valid_string(64, race_type))
i_r_french_regulation    events    16395_16531_1_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_1_not_null    id IS NOT NULL
i_r_french_regulation    events    16395_16531_2_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_2_not_null    occurred_at IS NOT NULL
i_r_french_regulation    events    16395_16531_3_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_3_not_null    operator_id IS NOT NULL
i_r_french_regulation    events    16395_16531_4_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_4_not_null    player_id IS NOT NULL
i_r_french_regulation    events    16395_16531_5_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_5_not_null    player_ip IS NOT NULL
i_r_french_regulation    events    16395_16531_6_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_6_not_null    session_id IS NOT NULL
i_r_french_regulation    events    16395_16531_7_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_7_not_null    has_player_confirmation IS NOT NULL
i_r_french_regulation    events    16395_16531_8_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_8_not_null    purchase_id IS NOT NULL
i_r_french_regulation    events    16395_16531_9_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_9_not_null    event_label_as_shown_to_player IS NOT NULL
i_r_french_regulation    events    16395_16531_18_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_18_not_null    race_type IS NOT NULL
i_r_french_regulation    events    16395_16531_19_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_19_not_null    bet_name_as_shown_to_player IS NOT NULL
i_r_french_regulation    events    16395_16531_20_not_null    i_r_french_regulation    events    horse_racing_purchase_event    CHECK    NO    NO    i_r_french_regulation    events    16395_16531_20_not_null    event_date IS NOT NULL

Best regards,

Hristo Ivanov