OCP-1Z0-新051-61题版本-2
QUESTION NO: 2
You need to design a student registration database that contains several tables storing academic information.
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID.The STUDENT_ID column in the STUDENTS table is a primary key.
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?
A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa
NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY
students(student_id));
B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa
NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa
NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa
NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id)
students(student_id));
Answer: D
答案解析：
参考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52238
dept_20 table and defines and enables a foreign key on the department_id column that references the primary key on the department_id column
of the departments table:
fk_deptno ensures that all departments given for employees in the dept_20 table are present in the departments table. However, employees can have null department numbers, meaning
they are not assigned to any department. To ensure that all employees are assigned to a department, you could create a NOT NULL constraint on the department_id column in the dept_20 table in addition to the REFERENCES constraint.
department_id column of the departments table as a primary or unique key.
FOREIGN KEY clause, because the constraint is defined inline. The data type of the department_id column is not needed, because Oracle
automatically assigns to this column the data type of the referenced key.
CREATE TABLE dept_20
(employee_id NUMBER(4),
last_name VARCHAR2(10),
job_id VARCHAR2(9),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(7,2),
commission_pct NUMBER(7,2),
department_id,
ON DELETE clause, causing Oracle to prevent the deletion of a department if any employee works in that department.