Postgresql - Remove tuple referenced by another table

Question

I’m having a problem that I didn’t find anything in Google (maybe I didn’t know to ask him)

Just imagine the following:

• I have 11 tables, one call tbl_pai and 10 other calls tbl_filha1, tbl_filha2, tbl_daughter{n}.

• In the daughter tables has a field called id_pai and a Constraint (CONSTRAINT fk_filha_com_pai FOREIGN KEY (id_pai) REFERENCES tbl_pai(id))

That is, 10 tables that have foreign keys referenced to the main table

The question is: How can I elegantly test whether or not I can remove a tuple from the main table? I could only think of two options: Try to delete the parent record and treat the error if the record is referenced in another table, or, make a COUNT in all daughter tables to know if there is dependency.

I need that information to decide whether or not to release a DELETE in my application. If the record has no reference, I display the button, if not I hide the button. The problem is that this button is in a listing (of 'parent' records) and this check needs to be done for each record. I did using the COUNT but was extremely slow as the 'parent' table has over a million records.

There is a less costly method?

Personal thank you.

Answer 1

He set up a function for that :

CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION checkDependence(VARCHAR, VARCHAR, INTEGER) RETURNS BOOLEAN AS $$
DECLARE 
    tableName   ALIAS FOR $1;
    columnName  ALIAS FOR $2;
    id          ALIAS FOR $3;

    sqlExecute  RECORD;
    sqlExecute2 RECORD;
    sqlQuery    VARCHAR;
    sqlQuery2   VARCHAR;
    primaryKey  VARCHAR;
BEGIN

    sqlQuery := 'SELECT table_name';
    sqlQuery := sqlQuery || ' FROM information_schema.columns';
    sqlQuery := sqlQuery || ' WHERE table_name != '''||tableName||'''';
    sqlQuery := sqlQuery || ' AND column_name = '''||columnName||'''';

    FOR sqlExecute IN EXECUTE(sqlQuery) LOOP

        RAISE NOTICE '%', sqlExecute.table_name;
        RAISE NOTICE '%', columnName;
        RAISE NOTICE '%', id;

        sqlQuery2 := 'SELECT CASE WHEN count(1) > 0 THEN TRUE ELSE FALSE END AS has';
        sqlQuery2 := sqlQuery2 || ' FROM '||sqlExecute.table_name;
        sqlQuery2 := sqlQuery2 || ' WHERE '||columnName||' = '||id;

        FOR sqlExecute2 IN EXECUTE(sqlQuery2) LOOP
            RAISE NOTICE '%', sqlExecute2;
            IF sqlExecute2.has THEN
                RETURN TRUE;
            END IF;
        END LOOP;
    END LOOP;

    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

Now you only need to call her in your select :

SELECT
    checkDependence('table_pai', 'id', id);

Answer 2

Do count can be slow. Solution with the exists:

select *,
    not (
        exists (
            select 1
            from telemetria
            where id_sistema = s.id
        ) or
        exists (
            select 1
            from alerta
            where id_sistema = s.id
        ) or
        exists (
            select 1
            from controle
            where id_sistema = s.id
        ) or
        exists (
            select 1
            from cliente_sistema
            where id_sistema = s.id
        )
    ) as pode_deletar
from sistema s

If you get good performance can also be transformed into function

Answer 3

I would do everything in one table, using a field called filhode with a foreign key for the table id itself. I even use this to make menus. See an example:

CREATE TABLE menu
(
  idmenu serial NOT NULL,
  menu character varying(50),
  filhode integer,
  CONSTRAINT pk_idmenu PRIMARY KEY (idmenu),
  CONSTRAINT fk_filhode FOREIGN KEY (filhode)
      REFERENCES menu(idmenu) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
)

With that I don’t need this heap daughters tables like tbl_filha1, tbl_filha2 ... tbl_daughterx. Everything happens in a single table.

Answer 4

If the rows of the daughter tables you’re checking aren’t important, I mean, if you just need to consult the existence of daughters referencing the parent you’re trying to exclude, you can use something like:

SELECT 1 FROM tbl_filha1 WHERE id_pai = <id do pai>;

[...]

SELECT 1 FROM tbl_filhaN WHERE id_pai = <id do pai>;

Note that you will have to do this for the N daughter tables (at least until you find a result) which would be a bit onerous. Considering this, in its place I would do the routine for exclusion of the parent table and treat the error in the application if the DBMS bar the exclusion, without prior checking of daughter tables.

Answer 5

Try the following:

SELECT s.*, 

CASE WHEN 
    t.id_sistema IS NOT NULL OR
    c.id_sistema IS NOT NULL OR
    cs.id_sistema IS NOT NULL 
    THEN 'disabled'

END as desabilitar 

FROM sistema s
    LEFT JOIN telemetria t ON s.id = t.id_sistema
    LEFT JOIN controle c ON s.id = c.id_sistema
    LEFT JOIN cliente_sistema cs ON s.id = cs.id_sistema

The code above will list all your systems making a left Join with the other tables. This will cause all independent systems to be listed if there is id_system in the other tables.

CASE WHEN is checking if id_system in the other tables is different from null. If it is not null, it creates the column disable = 'disabled'.

Now in your HTML, in the system listing, use the disable field to assign the disabled to the button.

<button <?= $rs['desabilitar'] ?>>DELETAR</button>

If you are using link tag, change the value of the disabled column to 'None', and then you hide the link with a css inline:

<a style="display:<?=$rs['desabilitar']?>">

This is to avoid the IF. But you can return true and do an if checking to disable = true, hides the button.

But as you said there are millions of records, maybe the best option is to treat the bank exception when you try to delete.

But it is worth trying this solution =)

I hope I’ve helped!