2

If I have two tables;

Table A
id | etc
1  | etc
2  | etc
etc

And

Table B
id | A_id | etc
1  | 2    | etc
2  | 2    | etc
3  | 1    | etc
etc

The relationship between Table A and Table B is defined in the database as a foreign key with cascading delete. Do I still have to explicitly declare join columns between these tables? Several ORMs that I have previously worked with do not require this (for example the PHP Silverlight ORM), and I was thinking that if you requested unambiguous column names, why not? It seems to me that defining FK relationships should automatically define which columns to join a table one

For example, I want to know if I can write this query:

SELECT * FROM [Table B] B LEFT OUTER JOIN [Table A] A ON B.A_id = A.id

As

SELECT * FROM [Table B] B LEFT OUTER JOIN [Table A] A
  • 2
    Several ORMs do not require this, right. Because they add the joining condition themselves. But SQL Server is not an ORM, it's a DBMS. What would you expect (the ORM or SQL Server) to do if you have 2 FK constraints between the tables? – ypercubeᵀᴹ Dec 12 '16 at 17:47
  • 1
    And for the record, I think this is a useful question. It is related to NATURAL JOIN which (for good or for bad) has not been implemented in SQL Server. – ypercubeᵀᴹ Dec 12 '16 at 17:48
2

You always have to specify the columns you are joining on, regardless of any foreign key relationships.

Why? Because otherwise you couldn't do a full outer join if a foreign key existed between the tables, which would lead to identical queries producing different results for non-obvious reasons.

Also, what if you explicitly join on a different field? Would SQL behave as if you had included both fields in the join statement? Would you have to somehow tell it to ignore the foreign-key relationship for this one query?

FYI, the term "implicit join" in SQL actually refers to something entirely different, a SELECT statement made using a comma instead of a JOIN keyword:

 SELECT *
 FROM table_A, table_B
 WHERE table_A.key = table_B.key

vs an "Explicit" join:

 SELECT *
 FROM table_A JOIN table_B
 ON table_A.key = table_B.key

(This "old style" of implicit joins still works, but is not recommended.)

1

Yes, you still have to explicitly declare the join columns.

0

You have to specify a condition to join on between the tables, a foreign key constraint does not provide this criteria but it does help the developer / DBA understand the relationship between the tables.

Reference

We can see this via this example as well:

CREATE TABLE test.dbo.tablea
(Id INT PRIMARY KEY , etc VARCHAR(255))

CREATE TABLE test.dbo.tableb
(Id INT PRIMARY KEY , A_id INT, etc VARCHAR(255))

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.tablea SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.tableb ADD CONSTRAINT
    FK_tableb_tablea FOREIGN KEY
    (
    A_id
    ) REFERENCES dbo.tablea
    (
    Id
    ) ON UPDATE  CASCADE 
     ON DELETE  CASCADE 

GO
ALTER TABLE dbo.tableb SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

INSERT into dbo.tablea (id,etc)
VALUES
(1,'abc'),
(2,'def'),
(3,'ghi')

INSERT INTO dbo.tableb
(id, A_id, etc)
VALUES
(4,1,'aaa'),
(5,2,'bbb'),
(6,3,'ccc')

SELECT 
a.id,
b.A_id,
b.id,
a.etc,
b.etc
FROM test.dbo.tablea a
JOIN test.dbo.tableb b

You will receive a join error. You can do joins via the where clause or with the join on operator, but you must pick.

Foreign keys are meant to keep the referencial integrity of your data in check. More on FK

0

You have to specify all join conditions. FK's are usually used for referencail integrity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.