Postgresql – Constraint Information – PostgreSQL 9.2

constraintdeleteerrorspostgresql

I was trying to delete some data from the table dm.billables:

1 – Select the id – PASS

SELECT * INTO junk.clientid_10306 FROM dm.billables WHERE account_id = 10306;

2 – Delete the data from the table below – PASS

DELETE FROM dm.billables_links WHERE billable_id IN (select billable_id FROM junk.clientid_10306);

3 – Delete data from the table below – FAIL

DELETE FROM dm.billables WHERE billable_id IN (select billable_id FROM junk.clientid_10306);

ERROR:

ERROR:  update or delete on table "billables" violates foreign key constraint "fk_billable_must_exist" on table "billables"
DETAIL:  Key (billable_id)=(14188857) is still referenced from table "billables".

Constraint:

ALTER TABLE dm.billables_links
  ADD CONSTRAINT fk_billable_must_exist FOREIGN KEY (billable_id)
      REFERENCES dm.billables (billable_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

There is no data on dm.billables_link anymore. Why am I getting the error though?

Table dm.billables:

CREATE TABLE
    billables
    (
        billable_id BIGINT DEFAULT "nextval"('"dm"."billables_billable_id_seq"'::"regclass") NOT
        NULL,
        account_id BIGINT NOT NULL,
        code CHARACTER VARYING(64) NOT NULL,
        info "TEXT",
        m_unit USER-DEFINED,
        m_unit_custom CHARACTER VARYING(64),
        unit_cost NUMERIC(16,4),
        tax_aggregate_id_cost BIGINT,
        unit_price NUMERIC(16,4),
        tax_enabled_price BOOLEAN DEFAULT true,
        tax_aggregate_id_price BIGINT,
        ts_created TIMESTAMP(6) WITH TIME ZONE DEFAULT "transaction_timestamp"() NOT NULL,
        ts_modified TIMESTAMP(6) WITH TIME ZONE DEFAULT "transaction_timestamp"() NOT NULL,
        ts_last_used TIMESTAMP(6) WITH TIME ZONE,
        is_demo BOOLEAN DEFAULT false NOT NULL,
        CONSTRAINT pk_billables PRIMARY KEY (billable_id),
        CONSTRAINT fk_price_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_price)
        REFERENCES tax_aggregates (tax_aggregate_id),
        CONSTRAINT fk_cost_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_cost) REFERENCES
        tax_aggregates (tax_aggregate_id),
        CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
        CONSTRAINT cc_m_unit_either_ref_or_custom CHECK (ARRAY[("m_unit" IS NOT NULL),
        ("m_unit_custom" IS NOT NULL)] <> ARRAY[true, true])
    );

Table dm.billalbes_link:

CREATE TABLE
    billables_links
    (
        billable_link_id BIGINT DEFAULT "nextval"('"dm"."billables_links_billable_link_id_seq"'::
        "regclass") NOT NULL,
        billable_id BIGINT NOT NULL,
        customer_id BIGINT,
        role_id BIGINT,
        mobiuser_id BIGINT,
        CONSTRAINT pk_billables_links PRIMARY KEY (billable_link_id),
        CONSTRAINT fk_billable_must_exist FOREIGN KEY (billable_id) REFERENCES billables
        (billable_id),
        CONSTRAINT fk_customer_must_exist FOREIGN KEY (customer_id) REFERENCES
        dbname_live.public.ja_customers (id),
        CONSTRAINT fk_role_must_exist FOREIGN KEY (role_id) REFERENCES dbname_live.public.ja_role
        (id),
        CONSTRAINT cc_one_and_only_one_target CHECK ((((("customer_id" IS NOT NULL))::INTEGER + (
        ("role_id" IS NOT NULL))::INTEGER) + (("mobiuser_id" IS NOT NULL))::INTEGER) = 1)
    );

Best Answer

The problem was that I was using PGADMIN to see the constraints and references.

When I used the \d+ command, I was able to see the other references better.

So, There was another table that had the same data, and I had to delete them first.

Once I did it, I was able to delete the data I wanted.

Related Question