tags:

views:

114

answers:

0

I know I can do

PRAGMA foreign_key_list('users')

in sqlite to get all the tables that users reference. But how do I get a list of all the tables that reference users?

For instance:

CREATE TABLE roles (
    role_id integer PRIMARY KEY
);

CREATE TABLE users (
    user_id integer PRIMARY KEY
);

CREATE TABLE user_roles (
    user_id integer NOT NULL,
    role_id integer NOT NULL,
    PRIMARY KEY(user_id, role_id),
    FOREIGN KEY(user_id) REFERENCES users(user_id), 
    FOREIGN KEY(role_id) REFERENCES roles(role_id)
);

Doing

PRAGMA foreign_key_list('user_roles') 

will show both users and roles, but how do I figure out which table(s) reference users, in the example above would be user_roles.