Foreign Key 'walker'?

On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote:

Hi group,

Considering following (simplified) example:

CREATE TABLE tblnr1(
nr1id SERIAL PRIMARY KEY,
firstname TEXT
);
CREATE TABLE tblnr2(
nr2id SERIAL PRIMARY KEY,
nr1id INTEGER REFERENCES tblnr1(nr1id)
);
CREATE TABLE tblnr3(
nr3id SERIAL PRIMARY KEY,
nr2id INTEGER REFERENCES tblnr2(nr2id)
);

Suppose I want to delete a record in tblnr1.
Does Postgres has some command/procedure/function to list tables
that have FK constraints on that table (tblnr1)
and lists also the tables that have a FK constraint on tables that
have a FK constraint on the first? etc.
So I would like some kind of FK 'walker'.

I want this because:
1) I hate DELETE CASCADE because I am chicken (So I use a script to
delete all related records in the right order in a transaction)
2) I have a lot of tables and am afraid I miss some. And I am also a
bit lazy .-)

Why not use something like this??

CONSTRAINT fk_tname FOREIGN KEY (nr2id)
REFERENCES tblnr2 MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO
ACTION,

Erwin Moller, 18.11.2008 15:47:

Suppose I want to delete a record in tblnr1.
Does Postgres has some command/procedure/function to list tables that
have FK constraints on that table (tblnr1)

That could be resolved with a query against the INFORMATION_SCHEMA

Something like

SELECT c.table_name || ' references ' || tu.table_name
FROM information_schema.table_constraints c
JOIN information_schema.constraint_table_usage tu
ON (c.constraint_name = tu.constraint_name AND c.table_schema = tu.table_schema)
WHERE c.constraint_type = 'FOREIGN KEY';

and lists also the tables that have a FK constraint on tables that have
a FK constraint on the first? etc.
So I would like some kind of FK 'walker'.

That would be your front-end that displays that (or some nested self-join using the above statement)

Regards
Thomas

ries van Twisk schreef:

On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote:

Hi group,

Considering following (simplified) example:

CREATE TABLE tblnr1(
nr1id SERIAL PRIMARY KEY,
firstname TEXT
);
CREATE TABLE tblnr2(
nr2id SERIAL PRIMARY KEY,
nr1id INTEGER REFERENCES tblnr1(nr1id)
);
CREATE TABLE tblnr3(
nr3id SERIAL PRIMARY KEY,
nr2id INTEGER REFERENCES tblnr2(nr2id)
);

Suppose I want to delete a record in tblnr1.
Does Postgres has some command/procedure/function to list tables that
have FK constraints on that table (tblnr1)
and lists also the tables that have a FK constraint on tables that
have a FK constraint on the first? etc.
So I would like some kind of FK 'walker'.

I want this because:
1) I hate DELETE CASCADE because I am chicken (So I use a script to
delete all related records in the right order in a transaction)
2) I have a lot of tables and am afraid I miss some. And I am also a
bit lazy .-)

Why not use something like this??

CONSTRAINT fk_tname FOREIGN KEY (nr2id)
REFERENCES tblnr2 MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO
ACTION,

Hi,

Thankf for your reply, but that is NOT what I am looking for.

I want to list the tables that use REFERENCES etc. against my first table.
And deeper (so if other tables references second table then I would like
them too). etc.

Regards,
Erwin Moller

Thomas Kellerer schreef:

Erwin Moller, 18.11.2008 15:47:

Suppose I want to delete a record in tblnr1.
Does Postgres has some command/procedure/function to list tables that
have FK constraints on that table (tblnr1)

That could be resolved with a query against the INFORMATION_SCHEMA

Something like

SELECT c.table_name || ' references ' || tu.table_name
FROM information_schema.table_constraints c JOIN
information_schema.constraint_table_usage tu ON
(c.constraint_name = tu.constraint_name AND c.table_schema =
tu.table_schema)
WHERE c.constraint_type = 'FOREIGN KEY';

Hi Thomas,

Thanks!
That is not excactly what I need, but it gets very close. :-)
I think I can use that approach to build a (PHP)script to do the
recursive trick, so it lists all tables that are coupled via-via to the
first table.
Should be straightforward from here.

Thank you.
Regards,
Erwin Moller

ries van Twisk wrote:

On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote:

Hi group,

Considering following (simplified) example:

<snip>

Suppose I want to delete a record in tblnr1.
Does Postgres has some command/procedure/function to list tables that
have FK constraints on that table (tblnr1)

The data you are looking for is stored in the system catalogs.
http://www.postgresql.org/docs/8.3/interactive/catalogs.html
You should be able to come up with some SELECT's to get what you want.

and lists also the tables that have a FK constraint on tables that
have a FK constraint on the first? etc.
So I would like some kind of FK 'walker'.

I want this because:
1) I hate DELETE CASCADE because I am chicken (So I use a script to
delete all related records in the right order in a transaction)

That isn't being chicken it is being silly (or is that just stubborn ;).
PostgreSQL is designed to delete related records that you tell it to
delete. Let it do what it is suppose to do.

2) I have a lot of tables and am afraid I miss some. And I am also a
bit lazy .-)

If your lazy why do all this manual work when you can leave it
automated? It won't miss a related record after you tell it to cascade
delete. This is an old well tested feature that you can rely on.

That probably all sounds more aggressive than it should. Not having a
strong dig at you but I do want to emphasise the fact that you shouldn't
waste your time doing manually what the software is designed to do.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Shane Ambler schreef:

ries van Twisk wrote:

On Nov 18, 2008, at 9:47 AM, Erwin Moller wrote:

Hi group,

Considering following (simplified) example:

<snip>

Suppose I want to delete a record in tblnr1.
Does Postgres has some command/procedure/function to list tables
that have FK constraints on that table (tblnr1)

The data you are looking for is stored in the system catalogs.
http://www.postgresql.org/docs/8.3/interactive/catalogs.html
You should be able to come up with some SELECT's to get what you want.

and lists also the tables that have a FK constraint on tables that
have a FK constraint on the first? etc.
So I would like some kind of FK 'walker'.

I want this because:
1) I hate DELETE CASCADE because I am chicken (So I use a script to
delete all related records in the right order in a transaction)

That isn't being chicken it is being silly (or is that just stubborn ;).
PostgreSQL is designed to delete related records that you tell it to
delete. Let it do what it is suppose to do.

Hi,

No, that is not the kind of chicken I was talking about. ;-)
My chicken is more along these lines:
I often have some tables to which everything is related (eg tblcourse
that contains everything belonging to a certain course).
I don't want to make a single simple mistake that if I accidentally
delete an entry there, I lose all underlying data via the CASCADE.
That is why I decided never to use CASCADE, and simply do it by myself.
No big deal except that I have to find out the related tables.
I rather have a FK constraint violation error than an empty DB.

Hence my question.
I am not afraid that Postgres will screw up somehow.
That actually NEVER happened in all the years I am using it. Try that
with MSSQL or MySQL. I love Postgres. ;-)

2) I have a lot of tables and am afraid I miss some. And I am also a
bit lazy .-)

If your lazy why do all this manual work when you can leave it
automated? It won't miss a related record after you tell it to cascade
delete. This is an old well tested feature that you can rely on.

I described above.

That probably all sounds more aggressive than it should. Not having a
strong dig at you but I do want to emphasise the fact that you
shouldn't waste your time doing manually what the software is designed
to do.

No problem at all.
I totally agree with you.
I only have this fear I screw up (not Postgresql) if I use CASCADE and
accidentally delete a 'high' record in the chain.

Regards,
Erwin Moller

Hello

I used this code

CREATE OR REPLACE FUNCTION list_user_tables_sort_depend
(owner VARCHAR, revers BOOLEAN) RETURNS SETOF VARCHAR AS '
DECLARE tabulky VARCHAR[]; i INTEGER; opakovat BOOLEAN = ''t'';
pom VARCHAR; exportovano VARCHAR[] = ''{}''; r RECORD;
mohu_exportovat BOOLEAN;

BEGIN SELECT ARRAY(SELECT tablename FROM pg_tables WHERE tableowner =
owner) INTO tabulky;
WHILE opakovat LOOP
opakovat := ''f'';
FOR i IN array_lower(tabulky,1) .. array_upper(tabulky,1) LOOP
IF tabulky[i] <> '''' THEN
mohu_exportovat := ''t'';
FOR r IN SELECT t.relname AS z, x.relname AS nz FROM
pg_catalog.pg_constraint d
INNER JOIN pg_catalog.pg_class t on t.oid = d.conrelid
INNER JOIN pg_catalog.pg_class x on x.oid = d.confrelid
WHERE d.contype = ''f'' AND t.relname = tabulky[i] LOOP
IF NOT r.nz = ANY(exportovano) THEN
mohu_exportovat := ''f'';
END IF;
END LOOP;
IF mohu_exportovat THEN
pom := tabulky[i];
exportovano := exportovano || tabulky[i];
opakovat := ''t''; tabulky[i] := '''';
END IF;
END IF;
END LOOP;
END LOOP;
IF revers THEN
FOR i IN REVERSE array_upper(exportovano,1) ..
array_lower(exportovano,1) LOOP
RETURN NEXT exportovano[i];
END LOOP;
ELSE
FOR i IN array_lower(exportovano,1) .. array_upper(exportovano,1) LOOP
RETURN NEXT exportovano[i];
END LOOP;
END IF;
RETURN;
END;
' LANGUAGE plpgsql;

sorry, identifiers are in czech

regards
Pavel Stehule

2008/11/18 Erwin Moller <erwin@darwine.nl>:

Erwin Moller wrote:

No, that is not the kind of chicken I was talking about. ;-)
My chicken is more along these lines:
I often have some tables to which everything is related (eg tblcourse
that contains everything belonging to a certain course).
I don't want to make a single simple mistake that if I accidentally
delete an entry there, I lose all underlying data via the CASCADE.

OK, so the idea is to prevent DELETEs of records with existing
relationships, except when invoked via some sort of script or wrapper
that says, essentially, "Yes, I really do mean delete this record and
all its related records, I'm not just accidentally issuing a DELETE".

Personally, if I had to do this I'd do this with a PL/PgSQL function
that dug through pg_catalog or INFORMATION_SCHEMA to do a depth-first
search and delete of the related records. Frankly, though, it sounds
horrible, and if you run into a relationship loop you're in a real mess.
At least the latter problem can't bite you unless you use DEFERRED
constraints.

No problem at all.
I totally agree with you.
I only have this fear I screw up (not Postgresql) if I use CASCADE and
accidentally delete a 'high' record in the chain.

To me, that sounds like you might have some of your relationships
backwards. Generally I wouldn't want to set an ON DELETE CASCADE
relationship on a parent record (not does it usually make any sense) ;
rather, the relationship on the child record will have ON DELETE CASCADE
set so the child will be deleted if the parent is. Deleting a child
record should only delete the child record, never cascade up to a parent.

The child record is useless and meaningless without the parent, so this
is appropriate.

For a practical example in a course/student management tool: If you
delete a `student', and the student has `student_course' (an m:n mapping
table) entries referencing `course', you would not expect the course to
be deleted, only the student<->course relationship and the student. If
the course was deleted explicitly by the user, you'd expect the
student_course relationship to restrict the deletion if students were
still listed as taking the course. So, the natural definition would be:

CREATE TABLE student_course (
student_id INTEGER REFERENCES student(student_id) ON DELETE CASCADE,
course_id INTEGER REFERENCES course(course_id) ON DELETE NO ACTION,
PRIMARY KEY(student_id, course_id)
);

... which is pretty close to what you end up with if you just bang out
the obvious structure for the relationship.

There are odd cases where those relationships end up being reversed (or
at least bidirectional), and in those cases I do tend to avoid ON DELETE
CASCADE, instead providing functions, triggers or rules to clean up
appropriately.

--
Craig Ringer

Craig Ringer schreef:

Erwin Moller wrote:

No, that is not the kind of chicken I was talking about. ;-)
My chicken is more along these lines:
I often have some tables to which everything is related (eg tblcourse
that contains everything belonging to a certain course).
I don't want to make a single simple mistake that if I accidentally
delete an entry there, I lose all underlying data via the CASCADE.

OK, so the idea is to prevent DELETEs of records with existing
relationships, except when invoked via some sort of script or wrapper
that says, essentially, "Yes, I really do mean delete this record and
all its related records, I'm not just accidentally issuing a DELETE".

Personally, if I had to do this I'd do this with a PL/PgSQL function
that dug through pg_catalog or INFORMATION_SCHEMA to do a depth-first
search and delete of the related records. Frankly, though, it sounds
horrible, and if you run into a relationship loop you're in a real
mess. At least the latter problem can't bite you unless you use
DEFERRED constraints.

Hi Craig,

Thanks for your reply.

For clearity's sake: If I want to delete a record that is refered to, I
always simply first delete the 'lower' records that fit my criteria,
then the 'higher'.
So I often end up with a series of deletes, which when executed in that
right order, do the same as a CASCADE would do on the 'higher' record.

Reason is simply I rather hit a FK constraint than a cascading delete on
mistake.
(I am programming against postgresql in almost all my projects, thus
this mistake just happens from time to time.)

So, that is the way I prefer doing it: It keeps me sharp because I force
myself to always understand each relation in every table that in in 'the
chain'.
So I am NOT looking for help on writing such a wrapper/script/function
to do this, because I prefer doing it myself.

I do not mind making a few deletes (allthough they get more and more
complex if you have more levels).
I asked this weird question because it would come in handy if I could
get the list of tables that are connected via FK to my table in question.

Is this clear? Maybe I have a weird way of programming. ;-)

No problem at all.
I totally agree with you.
I only have this fear I screw up (not Postgresql) if I use CASCADE
and accidentally delete a 'high' record in the chain.

To me, that sounds like you might have some of your relationships
backwards. Generally I wouldn't want to set an ON DELETE CASCADE
relationship on a parent record (not does it usually make any sense) ;
rather, the relationship on the child record will have ON DELETE
CASCADE set so the child will be deleted if the parent is. Deleting a
child record should only delete the child record, never cascade up to
a parent.

I must have written very poorly, since that is NOT what I mean.
(I am not a native english speaker, so indulge me please).

I totally agree with the statement that a DELETE should NEVER cascade up
to the parent record, and I'll never design a database like that.

Possibly my poor understanding of CASCADE is the root of this confusion.
(I wrote already I never use it).
I thought that is I define a field in a table with 'ON DELETE CASCADE'
that means that ANY record in other tables that have a FK constraint on
this parenttable are also deleted.
And the same for child-child-tables, etc.

Do I have that right?

The child record is useless and meaningless without the parent, so
this is appropriate.

For a practical example in a course/student management tool: If you
delete a `student', and the student has `student_course' (an m:n
mapping table) entries referencing `course', you would not expect the
course to be deleted, only the student<->course relationship and the
student. If the course was deleted explicitly by the user, you'd
expect the student_course relationship to restrict the deletion if
students were still listed as taking the course. So, the natural
definition would be:

CREATE TABLE student_course (
student_id INTEGER REFERENCES student(student_id) ON DELETE CASCADE,
course_id INTEGER REFERENCES course(course_id) ON DELETE NO ACTION,
PRIMARY KEY(student_id, course_id)
);

... which is pretty close to what you end up with if you just bang out
the obvious structure for the relationship.

Well, that is excactly the way I work too. So we agree here.

To stick to this example: I was describing the situation I accidently
DELETED course_id in table course.
I don't want that that deletion cascades though the whole database and
deletes all related rows.

But maybe I misinterpret the way CASCADE works (see my explanation above).

Regards,
Erwin Moller

Hi Pavel,

Thanks for that.
But I already wrote a nice extension to my DB-class in PHP that uses
Thomas Kellerer's approach.
It was simple once you know how to retrieve the info from the
systemtables. :-)

Regards,
Erwin Moller

Pavel Stehule schreef:

Erwin Moller schreef:

Craig Ringer schreef:

Erwin Moller wrote:

No, that is not the kind of chicken I was talking about. ;-)
My chicken is more along these lines:
I often have some tables to which everything is related (eg
tblcourse that contains everything belonging to a certain course).
I don't want to make a single simple mistake that if I accidentally
delete an entry there, I lose all underlying data via the CASCADE.

OK, so the idea is to prevent DELETEs of records with existing
relationships, except when invoked via some sort of script or wrapper
that says, essentially, "Yes, I really do mean delete this record and
all its related records, I'm not just accidentally issuing a DELETE".

Personally, if I had to do this I'd do this with a PL/PgSQL function
that dug through pg_catalog or INFORMATION_SCHEMA to do a depth-first
search and delete of the related records. Frankly, though, it sounds
horrible, and if you run into a relationship loop you're in a real
mess. At least the latter problem can't bite you unless you use
DEFERRED constraints.

[Pfft my english sucks, I added a few clarifications]

Hi Craig,

Thanks for your reply.

For clearity's sake: If I want to delete a record that is refered to,
I always simply first delete the 'lower' records that fit my criteria,
then the 'higher'.
So I often end up with a series of deletes, which when executed in
that right order, do the same as a CASCADE would do on the 'higher'
record.

So that is the way I prefer to do the deletions.

Reason is simply I rather hit a FK constraint than a cascading delete
on mistake.
(I am programming against postgresql in almost all my projects, thus
this mistake just happens from time to time.)

So, that is the way I prefer doing it: It keeps me sharp because I
force myself to always understand each relation in every table that in
in 'the chain'.
So I am NOT looking for help on writing such a wrapper/script/function
to do this, because I prefer doing it myself.

I do not mind making a few deletes (allthough they get more and more
complex if you have more levels).
I asked this weird question because it would come in handy if I could
get the list of tables that are connected via FK to my table in question.

Is this clear? Maybe I have a weird way of programming. ;-)

No problem at all.
I totally agree with you.
I only have this fear I screw up (not Postgresql) if I use CASCADE
and accidentally delete a 'high' record in the chain.

To me, that sounds like you might have some of your relationships
backwards. Generally I wouldn't want to set an ON DELETE CASCADE
relationship on a parent record (not does it usually make any sense)
; rather, the relationship on the child record will have ON DELETE
CASCADE set so the child will be deleted if the parent is. Deleting a
child record should only delete the child record, never cascade up to
a parent.

I must have written very poorly, since that is NOT what I mean.
(I am not a native english speaker, so indulge me please).

I totally agree with the statement that a DELETE should NEVER cascade
up to the parent record, and I'll never design a database like that.

Possibly my poor understanding of CASCADE is the root of this
confusion. (I wrote already I never use it).
I thought that is I define a field in a table with 'ON DELETE CASCADE'
that means that ANY record in other tables that have a FK constraint
on this parenttable are also deleted.
And the same for child-child-tables, etc.

Typo again: The 'ON DELETE CASCADE' is not for a column (field) but for
the table.

Sorry to be so sloppy. ;-)

Regards,
Erwin Moller