SQL: How to make sure any primary key in any table "belongs to" a known Account ID (also PK) at the top of the hierarchy?

Question

Problem as follows:

I'm in charge of a creating a web services interface to a system with a huge database (hundreds of tables). The top level table in the database is "Accounts", with primary key Account_Id. Each row in any table can ultimately be traced back to a single account.

Each account should have access to their own account via the web services.

Let's say I create a web services function

DeleteProduct(string privateAccountKey, int productId);

How can I make sure - in a general an automatic manner - that the product they are trying to delete (who's primary key is productId) actually belongs to the account in question?

---

Update:

Thanks for your quick answers, but I need a more general solution:

Let's say we have 10 nested tables A,B,C,D,E,F,G,H,I,J. A is the top-level table, B has a foreign key into A, C has a foreign key into B, D has a foreign key into C, etc.

Given only a primary key of A and a primary key of J, we want to delete row in J. But we want to make sure that the row we delete "belongs to" the row in A identified by the supplied primary key of A. That is, we need to "join back" or "trace back" via the foreign keys from J back to A, find out which primary key in A the primary key from J is related to and compare it to the supplied primary key from A which we must make sure it is related to.

Can this be done in a general matter by using the system tables or something? No hardcoded joins and checks. It should work on any number of nested tables given only primary key of top-level table in tandem with primary key of bottom-level table (or any table in between).

---

Old:

I'm concernced that they could potentially send along product IDs which belongs to other accounts and those products would get deleted. The same problems exists with any other deletion or change of data.

What I need is a stored procedure which takes as input

a) A table name and the name of the primary key of this table

b) the primary key itself of a row in this table

c) the ID of the top-level primary key

and performs:

d) find out which account the primary key of b) belongs to

e) return true if the account from d) matches the ID (and account) from c)

E.g.:

PKBelongsToAccount(string table, string primaryKeyName, int ID, int accountId)

Pseudo:

DeleteProduct(string privateAccountKey, int productId)
{
    int accountId = FindAccountWhosPrivateKeyIs(privateAccountKey);
    if (!PKBelongsToAccount("Products", "Product_Id", productId, accountId))
    {
       return; 
    }
    else
    {
        // Product can safely be deleted
    }
}

Solve PKBelongsToAccount (as an SQL function/procedure). It needs to trace up the database relationships via the FK's until it gets to the Account table, find out which account it actually belongs to and compare this to the account ID of what we want it to belong to.

Perhaps there are better ways of doing it.

Sorry for the mess.

Answer 1

Here's my best attempt (w/o knowing the tables):

select * from products
--delete from products
where 
    productid = 1234
    and exists (select 1 
                from
                    account_products a 
                where 
                    a.productid = products.productid 
                    and a.account = 4321)

It uses exists to find it, since I don't know what DB you're using, so this should work across the board. Also, I commented out the delete so that you could select first to test that it's pulling back the right rows before firing the delete. It's a test I heartily recommend.

Answer 2

While I don't know what the schema is - you should be able to ascertain the existence of such a record before you actually delete it.

As in your example:

DeleteProduct(string privateAccountKey, int productId);

translates to:

"delete the product given: privateAccountKey and productId"

so you should be able to simply ascertain:

"select the account given the privateAccountKey and productId, using the predefined set of joins"

If this doesn't give you any results - you will know that this is an invalid command. You will obviously have to fill in the appropriate joins in the select statement.

This will need to be done for all your entities linked to an Account, unless you find some pattern of generating the SQL (again, you'll need to be more specific about your db schema if you want help with that question.)

Answer 3

The short answer is that yes, you could do what you want.

But, your question indicates that you are relying on creating a table per level of hierarchy, which is pretty much the worst way to do things, as you are finding out.

Using a method like materialized path/adjacency list would let you easily obtain the top-level parent ID, no matter how deep you go.

Answer 4

Metadata about tables, columns and constraints is available from the INFORMATION_SCHEMA views (which are supposedly an ISO standard). For example, foreign key constraints can be found by querying the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view.

The difficulty you will face, however, is that each table probably has multiple foreign key constraints, so how would you know which one eventually leads back to the Accounts table? In other words, which column links back to a table's immediate parent in the "hierarchy"? If there's an easy way to know this (e.g. always the first column in the table) then all well and good, but if not then the problem is equivalent to finding the path between two nodes in a graph (which I doubt could be answered with just a single SQL query...more like a 200 line stored procedure).

So I expect that a generalized routine based on INFORMATION_SCHEMA views alone would be daunting to implement. It would be easier and more robust to just create your own table to describe this metadata, something like:

CREATE TABLE hierarchy (
    ChildTable sysname,
    ParentTable sysname,
    ChildColumn sysname,
    ParentColumn sysname
)

Using this metadata, you could either run several JOINs to trace up the hierarchy until you reach the Accounts table, or else just construct a single multi-table JOIN to do the check.