0

I'm using Perl DBI with PostgreSQL, and my users will be deleting rows from a table, some of which can't be deleted because of foreign key constraints, which is fine, but I want a way to mark those rows on the front end so that it's known which rows have constraints before trying to delete them. Right now, a user would just hit "delete" and would either get an error or have the row deleted.

Dan Goodspeed
  • 3,484
  • 4
  • 26
  • 35
  • And that is exactly the intention of constraints. Constraints exist because a data model has to be _imposed_ one way or the other. Just "hitting delete" because you want to delete some record does not make sense. Ergo: the app should be aware of the data model. For example: if you try to "delete" a person and a marriage-record for that person exists, what should happen to the marriage record? – wildplasser Mar 13 '14 at 22:35
  • @wildplasser, I understand why constraints exist. My point is, I don't want to show a "delete user" button if it wouldn't work anyway. I only want it to show for users that don't have any constraints. But I don't know how other than to try to delete it and see if it returns an error. – Dan Goodspeed Mar 13 '14 at 23:34
  • possible duplicate of [Postgres: SQL to list table foreign keys](http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys) – Hart CO Mar 13 '14 at 23:47
  • Does your car have an "eject wheel" button? (maybe four of them) – wildplasser Mar 13 '14 at 23:47
  • 1
    @wildplasser Not sure I follow, seems like OP is asking how to find the constraints so that users don't even see the option to perform tasks they are restricted from doing. – Hart CO Mar 13 '14 at 23:51
  • 1
    IMHO the app should have an intimate relation with the database and the data model. This could be implemented by (application-)catalogs, but the app should be aware of the model. One way or the other. In any case "delete the record" is not an option. If _it_ is an intended operation, it should be implemented. One way or th other. – wildplasser Mar 13 '14 at 23:58
  • 1
    @GoatCO: there is a hidden "contract" beween the DB and the app. "finding the constraints" is (genearally) not the way to impose the contract. – wildplasser Mar 14 '14 at 00:00
  • @wildplasser, I actually don't care what the constraints are, I just want a boolean answer (either "yes this record can be deleted" or "no it cannot be deleted"). – Dan Goodspeed Mar 14 '14 at 00:09
  • "I actually don't care if a wheel can be ejected. I just want to know: can this wheel be ejected?" – wildplasser Mar 14 '14 at 00:10
  • 1
    Ok, you don't understand the question at all I guess. – Dan Goodspeed Mar 14 '14 at 00:17
  • It seems that you want to do [something like this](http://stackoverflow.com/questions/14357121/postgresql-get-all-the-rows-referencing-via-foreign-keys-a-particular-row-in). But normally, your app should know about all the constraints and you should check them explicitly like wildplasser tried to explain. – nwellnhof Mar 15 '14 at 15:59

2 Answers2

1

I'm not terribly familiar with PostgreSQL, but I believe you can use:

SELECT * 
FROM information_schema.table_constraints 
WHERE table_name = 'YourTable'

Update- Looks like a solid answer here: Postgres: SQL to list table foreign keys

Community
  • 1
  • 1
Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • Yes and something similar in other databases. – alexmac Mar 13 '14 at 22:40
  • I tried that and it only tells me about internal constraints (primary key column and unique columns). Nothing about foreign keys. – Dan Goodspeed Mar 13 '14 at 23:39
  • I copy/pasted the SQL statement from the link you provided, only changing the table name and it doesn't return anything. Am I supposed to change something else? – Dan Goodspeed Mar 14 '14 at 00:05
  • @DanGoodspeed That could mean that there are no foreign keys on the table you're looking at, does it return anything if you just use: `WHERE tc.table_name='mytable';`? – Hart CO Mar 14 '14 at 03:30
  • There's definitely constraints. It's a users table and if I try to delete a user who.. made a comment (or anything from a slew of other different site activities), it returns an SQL foreign key constraint error. And do you mean changing WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='auth_users' to WHERE tc.table_name='auth_users'? I made that change and got the same results as your original comment (primary key column and unique columns). – Dan Goodspeed Mar 14 '14 at 07:54
  • Sadly I know very little about PostgreSQL and don't have a sandbox instance to play with, hopefully someone else can weigh in. – Hart CO Mar 14 '14 at 15:36
0

So I figured it out on my own. For anyone in a similar situation, you can turn on transactions

$dbh->{AutoCommit} = 0;

And then try to delete the row, like this for example…

my $sth = $dbh->prepare("DELETE FROM auth_users WHERE username = ?");
$sth->execute($username);

And then check for an error...

if ( $sth->err ) { print "CAN'T be deleted.";}
else { print "CAN be deleted.";}

And then just roll back to not commit to the delete in case it could be deleted…

$dbh->rollback;

This is what works for me. If anyone has any better ideas, or some concerns with this method, feel free to share.

Dan Goodspeed
  • 3,484
  • 4
  • 26
  • 35