How to query/join tables where foreign key constraint is set in schema

sqlite

I'm relatively new to sqlite and databases in general.
I've successfully setup a schema of 4 tables, each having a foreign key pointing to its parent table. The schema also has the foreign key constraint set on the child keys.

Performing a query where every table is joined with the SELECT * FROM…JOIN…ON ……… works without issue, but it is laborious manually connecting the keys.

My question is: Is it possible to perform a query/join of the tables without the ON statement which links each parent/child key relationship? Since this was already established in the schema definition, this seems to be redundant. I'm trying to simplify the sql command and make use of the parent/child relationship already defined. When I perform said query but omit the ON statements, the results are not what I expected. Any guidance is appreciated.

Best Answer

A foreign key constraint is just a constraint, i.e., it prevents you from inserting data that would violate the constraint.

Foreign key constraints have no effect on queries; if you want to join tables through these values, you still have to write the join. (Why? Because the SQL standard has always said so, so changing it would break backwards compatibility.)

The only way to avoid the ON is by giving the columns the same name in the parent and child tables so that you can use the USING clause instead:

SELECT ... FROM ParentTable JOIN ChildTable USING (ParentID);

(And this is less typing than a hypothetical ... JOIN ... USING FOREIGN KEY ConstraintName.)

Related Solutions

“standard way” to log network messages (events) into a database

It seems that the very same question has been asked (and answered) on stackoveflow:

https://stackoverflow.com/questions/2797592/best-practice-logging-events-general-and-changes-database

sqlite – Querying for Hierarchical RBAC Scenario in SQLite

Here's what I came up with -- thanks to @CL. for the tips. I made it into a view so I can just query against that for a particular user.

CREATE VIEW user_permissions_deep AS
SELECT DISTINCT user_id, permission_id
FROM (
    SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role
    FROM (
        WITH RECURSIVE
          deep_roles(user_id, role_id, parent_id, depth) AS (
            SELECT ru.user_id, rr.role_id, rr.parent_id, 0 depth
            FROM role_roles rr
            INNER JOIN role_users ru ON rr.role_id = ru.role_id
            UNION
            SELECT dr.user_id, rr.role_id, rr.parent_id, dr.depth+1
            FROM deep_roles dr, role_roles rr
            WHERE rr.role_id=dr.parent_id
            AND dr.depth < 10
          )
        SELECT user_id, role_id, parent_id FROM deep_roles
        WHERE depth > 0
        UNION
        SELECT user_id, role_id, null parent_id
        FROM role_users
    ) AS urd
    INNER JOIN permission_roles pr
        ON pr.role_id = urd.role_id
        OR pr.role_id = urd.parent_id
    UNION
    SELECT user_id, permission_id, null role_id, null parent_id
    FROM permission_users
) AS upd
INNER JOIN permission p ON upd.permission_id = p.id;

I found I had to keep the depth check otherwise the loop would never exit if there was a cycle in the role inheritance chain.

If anyone has any suggestions on how this could be improved, please let me know!

Best Answer

Related Solutions

“standard way” to log network messages (events) into a database

sqlite – Querying for Hierarchical RBAC Scenario in SQLite

Related Question