Multiple choice

Which of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?

  1. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES dept deptno);

  2. CREATE TABLE EMP (empno NUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

  3. CRETE TABLE EM (empno NUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));

  4. CREATE TABLE EMP (empno NUMBER (4), ename VARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT emp deptno fk REFERENCES dept (deptno));
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

Option B correctly defines a FOREIGN KEY constraint using inline constraint syntax with the REFERENCES clause directly in the column definition. The syntax "deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno)" properly creates a foreign key named emp_deptno_fk that references the deptno column in the dept table. Option A is missing parentheses around the FOREIGN KEY keyword. Option C contains typos (CRETE instead of CREATE, EM instead of EMP) and incorrect constraint placement. Option D has incorrect keyword order with FOREIGN KEY placed after CONSTRAINT name instead of using the REFERENCES clause properly.

Keep practicing — related questions