This set of SQL Multiple Choice Questions & Answers (MCQs) focuses on “SQL Foreign Key”.
1. In SQL, which of the following constraint is used to establish a link between two tables?
a) PRIMARY KEY
b) LINK
c) FOREIGN KEY
d) CANDIDATE KEY
View Answer
Explanation: In SQL, the FOREIGN KEY constraint is used to establish a link between two tables. A foreign key in one table is used to point the primary key of another table. The PRIMARY KEY constraint is used to uniquely identify a record in a table. The candidate key is a set of keys that can become the primary key.
2. In SQL, the FOREIGN KEY of one table is the PRIMARY KEY of another table.
a) True
b) False
View Answer
Explanation: In SQL, the FOREIGN KEY of one table is the PRIMARY KEY of another table as it is used to create a link between two tables. It acts as a cross-reference between two tables as it references the primary key of another table. Every relationship in the database should be supported by a foreign key.
3. The table that defines the FOREIGN KEY is called as the parent table.
a) True
b) False
View Answer
Explanation: The table containing the foreign key is called the child table and the table containing the primary key is called the referenced or parent table. In other words, the table which contains the Foreign key as its primary key is the parent table and the other table which uses the foreign key is the child table.
4. Which of the following statement can be used to apply the FOREIGN KEY constraint in SQL?
a)
CREATE TABLE TABLE_NAME ( column1 datatype CONSTRAINT, column2 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY parent_table (column_name));
b)
CREATE TABLE TABLE_NAME ( column1 datatype CONSTRAINT, column2 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY REFERENCES parent_table (column_name));
c)
CREATE TABLE TABLE_NAME ( column1 datatype CONSTRAINT, column2 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY (column_name) REFERENCES (column_name));
d)
CREATE TABLE TABLE_NAME ( column1 datatype CONSTRAINT, column2 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY (column_name) REFERENCES parent_table (column_name));
Explanation: The statement that can be used to define a FOREIGN KEY in SQL is –
CREATE TABLE TABLE_NAME ( column1 datatype CONSTRAINT, column2 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY (column_name) REFERENCES parent_table (column_name));
The FOREIGN KEY and REFERENCES keyword is used when declaring a foreign key.
5. Which of the following statement is used to create the FOREIGN KEY constraint on an already existing table?
a)
ALTER TABLE TABLE_NAME ADD CONSTRAINT constraint_name FOREIGN KEY REFERENCES parent_table (column_name);
b)
ALTER TABLE TABLE_NAME ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES parent_table;
c)
ALTER TABLE TABLE_NAME ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES parent_table (column_name);
d)
ALTER TABLE TABLE_NAME ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES (column_name);
Explanation: The statement that is used to create a FOREIGN KEY on an already existing table is –
ALTER TABLE TABLE_NAME ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES parent_table (column_name);
We use the ALTER command with the ADD and CONSTRAINT keyword in this statement. Constraint name is a name given to the foreign key constraint by the user.
6. Which of the following statement can be used to delete the FOREIGN KEY constraint from a table?
a)
ALTER TABLE TABLE_NAME DROP FOREIGN KEY constraint_name;
b)
ALTER TABLE TABLE_NAME DROP FOREIGN KEY;
c)
ALTER TABLE TABLE_NAME DELETE FOREIGN KEY constraint_name;
d)
ALTER TABLE TABLE_NAME DELETE FOREIGN KEY;
Explanation: The statement that can be used to delete the FOREIGN KEY constraint from a table is –
ALTER TABLE TABLE_NAME DROP FOREIGN KEY constraint_name;
The ALTER command is used with the DROP keyword to delete the FOREIGN KEY. Here constraint_name was a name given to the foreign key by the user.
7. In SQL, a table can have more than one column with the FOREIGN KEY constraints.
a) True
b) False
View Answer
Explanation: In SQL, unlike the PRIMARY KEY constraint, a table can have more than one column with the FOREIGN KEY constraint. That means that a table can be connected to more than one table. Each table’s primary key will act as a foreign key for the child table.
8. Which of the following statement can be used to apply the FOREIGN KEY constraint on more than on column in a table?
a)
CREATE TABLE child_table ( column1 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY (col1, col2,...) REFERENCES (col1,col2));
b)
CREATE TABLE child_table ( column1 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY (col1) REFERENCES parent_table (col1), FOREIGN KEY (col2) REFERENCES parent_table (col2));
c)
CREATE TABLE child_table ( column1 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY (col1, col2,...) REFERENCES parent_table (col1,col2));
d)
CREATE TABLE child_table ( column1 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY (col1, col2,...) REFERENCES parent_table);
Explanation: The statement that can be used to apply the FOREIGN KEY constraint to more than column in a table is –
CREATE TABLE child_table ( column1 datatype CONSTRAINT, ... columnn datatype CONSTRAINT, FOREIGN KEY (col1, col2,...) REFERENCES parent_table (col1, col2));
Specify the FOREIGN KEY constraint to define one or more column as a foreign key and parent table with columns to which the foreign key columns refer.
Sanfoundry Global Education & Learning Series – SQL.
To practice all areas of SQL, here is complete set of 1000+ Multiple Choice Questions and Answers.
