0

The foreign key reference in the teradata with no check option behave differently for different statement.

  1. Select Count(*) or Select Count(1)
  2. Select *

I want to understand what is happening and is there a document explanation to it or is it simply a bug in Teradata.

Here is an example,

DDL Definition:

CT PARENTTB (
  IDP INTEGER NOT NULL,
  FNAME VARCHAR(10)
)
UNIQUE PRIMARY INDEX (IDP);

INSERT INTO PARENTTB VALUES (1,'PAWAN');
INSERT INTO PARENTTB VALUES (2,'SURAJ');

CT CHILDTB (
  IDC INTEGER NOT NULL,
  LNAME VARCHAR(10),
  FOREIGN KEY ( IDC ) REFERENCES WITH NO CHECK OPTION PARENTTB ( IDP )
)
UNIQUE PRIMARY INDEX (IDC);

INSERT INTO CHILDTB VALUES (1,'VISHWAKARMA');
INSERT INTO CHILDTB VALUES (2,'KULKARNI');
INSERT INTO CHILDTB VALUES (3,'SHARMA');

SELECT COUNT(*) FROM PARENTTB A INNER JOIN CHILDTB B ON A.IDP = B.IDC;
Result Count : 3

SELECT * FROM PARENTTB A INNER JOIN CHILDTB B ON A.IDP = B.IDC;
Result Count : 2

| IDP | FNAME | IDC | LNAME       |
| --- | ----- | --- | ----------- |
| 2   | SURAJ | 2   | KULKARNI    |
| 1   | PAWAN | 1   | VISHWAKARMA |

Can you guys explain why its behaving like this even when there is join on the same column (UPI in both tables but referenced in child)

My Analysis / Asumption:
For foreign key where it reference with no check option and non null values it joins it with self and returns the count only for statement select count(1) or select count(*).

Pawan Vishwakarma
  • 15
  • 4
  • 2
    The Foreign Key constraint in effect tells the optimizer that the COUNT query need not do the join to ParentTB, since you are not referencing any data from the parent and it "knows" (believes) the join will find exactly one parent for every child; so it just queries the ChildTB (look at the Explain). But if you retrieve the parent columns then it obviously must do the join. – Fred Jun 08 '23 at 20:59

0 Answers0