Query a Table's Foreign Key Relationships

Find foreign key relationships with a SQL query

No you cant,

But you can check it manually,it is cumbersome where you need to query each table and to check what are the columns[id's] exist in each different tables. To see it's relationships.

Regards

Query to match foreign key relationships

Assuming that pack_child (pack_id, item_sku), as well as order_detail (order_id, item_sku) are defined UNIQUE, this would work:

SELECT pc.pack_id, od.order_id
FROM   pack_child pc
LEFT   JOIN order_detail od USING (item_sku, item_quantity)
GROUP  BY 1, 2
HAVING count(*) = count(od.id)  -- every item of the pack has a match
AND    NOT EXISTS (
   SELECT
   FROM   order_detail od1
   LEFT   JOIN pack_child pc1 ON pc1.item_sku = od1.item_sku
                             AND pc1.item_quantity = od1.item_quantity
                             AND pc1.pack_id = pc.pack_id
   WHERE  od1.order_id = od.order_id
   AND    pc1.id IS NULL       -- and order has no additional item
   );

Returns all pairs of pack_id and order_id that match exactly.

db<>fiddle here

There are a hundred-and-one alternative ways to write the query. Which is the fastest depends on cardinalities, data distribution, constraints and, most importantly, available indexes.

It's a special application of relational-division. Here is an arsenal of techniques:

• How to filter SQL results in a has-many-through relation

One alternative, probably faster: create views or materialized views of the parent tables including the item count:

CREATE MATERIALIZED VIEW v_pack_master AS
SELECT *
FROM   pack_master
JOIN  (
   SELECT pack_id, count(*) AS items
   FROM   pack_child
   GROUP  BY 1
   ) c USING (pack_id);

CREATE MATERIALIZED VIEW v_customer_order AS
SELECT *
FROM   customer_order
JOIN  (
   SELECT order_id, count(*) AS items
   FROM   order_detail
   GROUP  BY 1
   ) c USING (order_id);

(Orders typically don't change later, so might be viable candidates for a materialized view.)

Only if there can be many order items, an index might pay (index expressions in this order):

CREATE INDEX foo ON v_customer_order (items, order_id);

The query now only considers orders with a matching item count to begin with:

SELECT * -- pack_id, order_id
FROM   v_pack_master pm
LEFT   JOIN v_customer_order co USING (items)
JOIN   LATERAL (
   SELECT count(*) AS items
   FROM   pack_child pc
   JOIN   order_detail od USING (item_sku, item_quantity)
   WHERE  pc.pack_id  = pm.pack_id
   AND    od.order_id = co.order_id
   ) x USING (items);

.. then, if all items match, we don't have to rule out additional items any more. And we have all columns from the parent table at our disposal right away, to return whatever it is you want to return ...

Query a Table's Foreign Key relationships

This should work (or something close):

select table_name
from all_constraints
where constraint_type='R'
and r_constraint_name in 
  (select constraint_name
  from all_constraints
  where constraint_type in ('P','U')
  and table_name='<your table here>'); 

How can I list all foreign keys referencing a given table in SQL Server?

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

If pktable_owner is not specified, the default table visibility rules
of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified
name, that table's columns are returned. If pktable_owner is not
specified and the current user does not own a table with the specified
pktable_name, the procedure looks for a table with the specified
pktable_name owned by the database owner. If one exists, that table's
columns are returned.

SQL Query using foreign key relationships

To get the average salaries of all departments . . .

SELECT Department, avg(Salary) AS avg_salary
FROM ProfessorsTable 
GROUP BY Department
ORDER BY avg_salary DESC;

To get only the department with the highest average . . .

SELECT Department, avg(Salary) AS avg_salary
FROM ProfessorsTable 
GROUP BY Department
ORDER BY avg_salary DESC LIMIT 1;

SQL-Query for a table with a foreign-key-field that references other foreign-key-fields

This query would work:

SELECT w.ID, worker, c.name AS `combined-Name`, d.department, l.department as 
location FROM workers w
LEFT JOIN combined c ON c.ID = w.combined
LEFT JOIN helper h ON h.ID = c.helper
LEFT JOIN departments d ON d.ID = h.department
LEFT JOIN location l ON l.ID = h.location
GROUP BY w.ID

I used the AS keyword to set the names to your preferred output.

This was tested locally using the provided structures and data.

It's basically 4 simple left joins, and then instead of selecting the ID's I select the name columns of the foreign tables.

The alias on c.name is quoted because we need to escape the special character -

How can I find out what FOREIGN KEY constraint references a table in SQL Server?

Here it is:

SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

This way, you'll get the referencing table and column name.

Edited to use sys.tables instead of generic sys.objects as per comment suggestion.
Thanks, marc_s

Sql queries using foreign key relationship

Your query should be more like...

$query="select passbook.isbn, issuedt, submitdt, bookname
    from passbook 
    INNER JOIN usertable on usertable.id=passbook.user_id 
    INNER JOIN book on passbook.isbn = book.isbn
    where usertable.username= '$username' ";

This links the user_id on passbook to the usertable id and does the selection of which user to limit with in the where clause instead.

Adding in a link to the books table means linking the passbook isbn to the book isbn field. The one thing to remember is that isbn is then on two tables being selected, so remember to add this to the fields selected (I've used the one from passbook, although they are both the same).

(Although you should be using prepared statements to select bu username.)

SQL Server 2000 - Query a Table’s Foreign Key relationships

SELECT o2.name
FROM sysobjects o
INNER JOIN sysforeignkeys fk on o.id = fk.rkeyid
INNER JOIN sysobjects o2 on fk.fkeyid = o2.id
WHERE o.name = 'foo'

---

Related Topics