Qualified naming… again

Back in December 2012 I blogged about a range of problems that can occur if columns referenced in queries are not qualified with the name or alias of the table that they belong to. This post is mostly a rehash of just one of those scenarios as it seems to crop up time and time again…

Suppose you are presented with the following:

DELETE
FROM   emp
WHERE  dept_id IN (SELECT dept_id FROM dept WHERE dept_name = 'Dept_5')

You would look at the above statement and think that it will do something along the lines of “delete the entries in EMP where they relate to a DEPT entry with the name of ‘Dept_5′”. You might be right… but if the tables were defined as follows you would be wrong:

CREATE TABLE dept
   (id         NUMBER (4) NOT NULL   
   ,dept_name  VARCHAR2 (30) NOT NULL)
/

ALTER TABLE dept
   ADD CONSTRAINT dept_pk
   PRIMARY KEY (id)
/

CREATE TABLE emp
   (id         NUMBER (4)     NOT NULL
   ,first_name VARCHAR2 (30)  NOT NULL
   ,last_name  VARCHAR2 (30)  NOT NULL
   ,dept_id    NUMBER (4)     NOT NULL)
/
   
ALTER TABLE emp
   ADD CONSTRAINT  emp_pk
   PRIMARY KEY (id)
/

ALTER TABLE emp
   ADD CONSTRAINT emp_fk1
   FOREIGN KEY (dept_id)
   REFERENCES dept (id)
/

If we were to populate these tables with 10 DEPT entries and 100 EMP entries and run our delete:

SQL>INSERT INTO dept
  2  SELECT ROWNUM
  3  ,      'Dept_' || TO_CHAR(ROWNUM)
  4  FROM   dual
  5  CONNECT BY ROWNUM <= 10
  6  /

10 rows created.

SQL>INSERT INTO emp
  2  SELECT ROWNUM
  3  ,      'E_' || TO_CHAR(ROWNUM)
  4  ,      'Employee_' || TO_CHAR(ROWNUM)
  5  ,      MOD(ROWNUM,10)+1
  6  FROM   dual
  7  CONNECT BY ROWNUM <= 100
  8  /

100 rows created.

SQL>COMMIT
  2  /

Commit complete.

SQL>DELETE
  2  FROM   emp
  3  WHERE  dept_id IN (SELECT dept_id
  4                     FROM   dept
  5                     WHERE  dept_name = 'Dept_5')
  6  /

100 rows deleted.

SQL>SELECT *
  2  FROM   emp
  3  /

no rows selected

Ummm… where’s my data gone?

Let’s rewrite the query to show what really happened:

DELETE
FROM   emp e
WHERE  e.dept_id IN (SELECT e.dept_id 
                     FROM   dept d
                     WHERE  d.dept_name = 'Dept_5')

Now we can see what went wrong. The DEPT tables does not contain a column named DEPT_ID so Oracle has referenced the column in the EMP table. It can do this as identifiers in an outer query are visible inside a sub-query. Hence when Oracle is unable to find an identifier local to the sub-query then it will expand its search to the outer query.

Our delete query therefore will delete any EMP entry where the DEPT_ID is not null if there exists an entry in DEPT with a DEPT_NAME of ‘Dept_5’. Since DEPT_ID is defined as NOT NULL and there does exist a DEPT entry named ‘Dept_5’ we end up deleting all entries from EMP. A rather nasty outcome.

The best way to prevent this type of a problem is to always qualify column names with the table name or table alias. If we had done that with our DELETE query we would have got the following:

SQL>DELETE
  2  FROM   emp e
  3  WHERE  e.dept_id IN (SELECT d.dept_id
  4                       FROM   dept d
  5                       WHERE  d.dept_name = 'Dept_5')
  6  /
WHERE  e.dept_id IN (SELECT d.dept_id
                            *
ERROR at line 3:
ORA-00904: "D"."DEPT_ID": invalid identifier

Our qualified reference would have resulted in an error. Even better, if the DELETE was included as part of a package then the package would have thrown a compilation error.

Echoing my advice from the previous post: always use qualified references in your queries. The effort is negligible so there’s no excuse not to.

Advertisement