Test Your MySQL Primary Key Knowledge with Our SQL Quiz

Progress

True / False

MySQL, a table can have multiple primary keys.

True False

MySQL, a table can have only one primary key, which may consist of single or multiple columns.

True / False

primary key in MySQL automatically creates a unique index for the column.

True False

When a primary key is defined, MySQL automatically creates a unique index for the primary key column(s).

True / False

MySQL, a primary key column cannot contain NULL values

True False

primary key column must contain unique values and cannot have NULL values, ensuring each record can be uniquely identified.

True / False

MySQL, a composite primary key is a primary key made up of multiple columns.

True False

composite primary key in MySQL is a primary key that consists of more than one column, used to uniquely identify a record.

True / False

You can alter a table to add a primary key after the table has been created in MySQL.

True False

You can use the ALTER TABLE statement to add a primary key to an existing table in MySQL

True / False

MySQL, using AUTO_INCREMENT with a primary key column automatically assigns a unique value to each new row.

True False

The AUTO_INCREMENT attribute in MySQL automatically generates a unique value for each new row inserted into a table

True / False

MySQL, you can define a primary key constraint on a view.

True False

Primary keys can only be defined on tables, not views, as views do not store data themselves.

True / False

MySQL, you can drop a primary key constraint using the DROP CONSTRAINT statement.

True False

MySQL, you drop a primary key constraint using the ALTER TABLE statement with DROP PRIMARY KEY.

True / False

In MySQL, a primary key can consist of up to 16 columns.

True False

In MySQL, a primary key can consist of up to 64 columns, as defined by the database constraints

Q10

True / False

In MySQL, a primary key is both a unique index and a clustered index by default.

True False

In MySQL, a primary key is implicitly a unique index, and in InnoDB (the default storage engine), it is also a clustered index, meaning the table data is stored in the order of the primary key.

Q11

Single Choice

What is the purpose of a primary key in a MySQL database?

To store images To uniquely identify each row in a table To encrypt dataTo speed up queries

A primary key in MySQL ensures that each row in a table can be uniquely identified, preventing duplicate entries and ensuring data integrity.

Q12

Single Choice

Which of the following statements correctly creates a primary key in a MySQL table?

CREATE TABLE users (id INT PRIMARY INDEX, name VARCHAR(50)); CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); CREATE TABLE users (id INT UNIQUE KEY, name VARCHAR(50));CREATE TABLE users (id INT KEY, name VARCHAR(50));

The correct syntax to define a primary key in MySQL is PRIMARY KEY.

Q13

Single Choice

Can a MySQL table have more than one primary key?

Yes No NullNull

A MySQL table can only have one primary key, but the primary key can consist of multiple columns (composite key).

Q14

Single Choice

How can you add a primary key to an existing MySQL table?

ALTER TABLE table_name ADD PRIMARY KEY (column_name); ALTER TABLE table_name ADD UNIQUE KEY (column_name); ALTER TABLE table_name MODIFY PRIMARY KEY (column_name);ALTER TABLE table_name CREATE PRIMARY KEY (column_name);

The correct syntax to add a primary key to an existing MySQL table is ALTER TABLE table_name ADD PRIMARY KEY (column_name);.

Q15

Single Choice

Which constraint automatically enforces uniqueness for a primary key in MySQL?

UNIQUE PRIMARY KEY NOT NULLINDEX

The PRIMARY KEY constraint in MySQL automatically enforces both uniqueness and the NOT NULL condition for the specified column(s).

Q16

Single Choice

What is a composite primary key in MySQL?

A primary key consisting of multiple columns A primary key with a composite data type A primary key shared by multiple tablesA primary key that is generated by the system

A composite primary key in MySQL is a primary key that consists of two or more columns, which together uniquely identify each row in the table.

Q17

Single Choice

How do you define a composite primary key when creating a table in MySQL?

CREATE TABLE example (id1 INT, id2 INT, PRIMARY INDEX (id1, id2)); CREATE TABLE example (id1 INT, id2 INT, PRIMARY KEY (id1, id2)); CREATE TABLE example (id1 INT, id2 INT, UNIQUE KEY (id1, id2));CREATE TABLE example (id1 INT, id2 INT, COMPOSITE KEY (id1, id2));

The correct syntax to define a composite primary key in MySQL is to list multiple columns within the PRIMARY KEY clause.

Q18

Single Choice

What happens if you try to insert a duplicate value into a column defined as the primary key in MySQL?

The duplicate value is accepted The row is ignored An error is generatedThe table is truncated

Attempting to insert a duplicate value into a primary key column in MySQL generates an error because primary keys must be unique.

Q19

Single Choice

In MySQL, how can you remove a primary key from a table?

ALTER TABLE table_name DROP KEY (column_name); ALTER TABLE table_name DELETE PRIMARY KEY; ALTER TABLE table_name REMOVE PRIMARY KEY;ALTER TABLE table_name DROP PRIMARY KEY;

The correct syntax to remove a primary key from a MySQL table is ALTER TABLE table_name DROP PRIMARY KEY;.

Q20

Single Choice

Which statement about primary keys and foreign keys in MySQL is correct?

A primary key in one table can be a foreign key in another table A foreign key in one table can be a primary key in another table A primary key and a foreign key must be defined on the same columnA primary key cannot be used as a foreign key

In MySQL, a primary key from one table can be referenced as a foreign key in another table to establish a relationship between the two tables.

Q21

Multiple Choice

Which of the following SQL statements correctly creates a table named 'customers' with 'customer_id' as the primary key?

SQL Code

CREATE TABLE customers (
 customer_id INT AUTO_INCREMENT,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 PRIMARY KEY (customer_id)
);

CREATE TABLE customers ( customer_id INT AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(50) ); CREATE TABLE customers ( customer_id INT, first_name VARCHAR(50), last_name VARCHAR(50), PRIMARY KEY (customer_id) ); CREATE TABLE customers ( customer_id INT AUTO_INCREMENT, first_name VARCHAR(50), PRIMARY KEY (customer_id) );CREATE TABLE customers ( customer_id INT AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(50), PRIMARY KEY (customer_id) );

In MySQL, the PRIMARY KEY constraint uniquely identifies each record in the table. The correct SQL statement should define the primary key after specifying the column data types.

Q22

Multiple Choice

Identify the correct SQL code that ensures a composite primary key for the 'order_items' table.

SQL Code

CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 quantity INT,
 PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id) ); CREATE TABLE order_items ( order_id INT, product_id INT, PRIMARY KEY (order_id) ); CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT );CREATE TABLE order_items ( order_id INT, product_id INT, PRIMARY KEY (product_id) );

A composite primary key consists of two or more columns used together as a unique identifier for the records. The code correctly defines a composite primary key for the 'order_items' table.

Q23

Multiple Choice

Which SQL statement is used to add a primary key constraint to an existing table named 'employees'?

SQL Code

ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

ALTER TABLE employees ADD PRIMARY KEY (employee_id); ALTER TABLE employees ADD CONSTRAINT PRIMARY KEY (employee_id); ALTER TABLE employees MODIFY PRIMARY KEY (employee_id);ALTER TABLE employees ADD CONSTRAINT pk_employee PRIMARY KEY (employee_id);

In MySQL, the ALTER TABLE statement is used to add a primary key constraint to an existing table. The correct syntax includes the 'ADD PRIMARY KEY' clause followed by the column name.

Q24

Multiple Choice

Choose the SQL code that correctly drops a primary key constraint from the 'orders' table.

SQL Code

ALTER TABLE orders
DROP PRIMARY KEY;

ALTER TABLE orders DROP PRIMARY KEY; ALTER TABLE orders DROP CONSTRAINT PRIMARY KEY; ALTER TABLE orders REMOVE PRIMARY KEY;ALTER TABLE orders DELETE PRIMARY KEY;

In MySQL, the 'ALTER TABLE' statement with the 'DROP PRIMARY KEY' clause is used to remove a primary key constraint from a table. The correct syntax ensures the primary key is removed without affecting other constraints.

Q25

Multiple Choice

Which SQL code snippet demonstrates how to handle a duplicate primary key error in a table insertion?

SQL Code

INSERT INTO users (user_id, username)
VALUES (1, 'alice');
INSERT INTO users (user_id, username)
VALUES (1, 'bob')
ON DUPLICATE KEY UPDATE username = 'bob';

INSERT INTO users (user_id, username) VALUES (1, 'alice'); INSERT INTO users (user_id, username) VALUES (1, 'bob'); INSERT INTO users (user_id, username) VALUES (1, 'alice') ON DUPLICATE KEY UPDATE username = 'bob';INSERT INTO users (user_id, username) VALUES (2, 'charlie');

In MySQL, the 'ON DUPLICATE KEY UPDATE' clause allows you to specify how to handle duplicate key errors, such as updating an existing record if a duplicate primary key is detected.

Q26

Multiple Choice

Select the SQL code that correctly implements a self-referencing primary key in a hierarchical table structure.

SQL Code

CREATE TABLE categories (
 category_id INT AUTO_INCREMENT,
 category_name VARCHAR(100),
 parent_category_id INT,
 PRIMARY KEY (category_id),
 FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
);

CREATE TABLE categories ( category_id INT AUTO_INCREMENT, category_name VARCHAR(100), parent_category_id INT, PRIMARY KEY (category_id), FOREIGN KEY (parent_category_id) REFERENCES categories(category_id) ); CREATE TABLE categories ( category_id INT AUTO_INCREMENT, category_name VARCHAR(100), PRIMARY KEY (category_id) ); CREATE TABLE categories ( category_id INT, category_name VARCHAR(100), parent_category_id INT, PRIMARY KEY (category_id) );CREATE TABLE categories ( category_id INT, category_name VARCHAR(100), PRIMARY KEY (category_id), FOREIGN KEY (category_id) REFERENCES categories(parent_category_id) );

A self-referencing primary key, also known as a recursive foreign key, is used in hierarchical table structures where a record in a table references another record in the same table. The code correctly implements this relationship in the 'categories' table.

Q27

Multiple Choice

Identify the SQL code that ensures data integrity by preventing null values in a primary key column.

SQL Code

CREATE TABLE employees (
 employee_id INT NOT NULL AUTO_INCREMENT,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 PRIMARY KEY (employee_id)
);

CREATE TABLE employees ( employee_id INT AUTO_INCREMENT, first_name VARCHAR(50), PRIMARY KEY (employee_id) ); CREATE TABLE employees ( employee_id INT NOT NULL, first_name VARCHAR(50), last_name VARCHAR(50), PRIMARY KEY (employee_id) ); CREATE TABLE employees ( employee_id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(50), PRIMARY KEY (employee_id) );CREATE TABLE employees ( employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50) );

In MySQL, a primary key automatically implies NOT NULL, ensuring that no null values can be entered into the primary key column. The code snippet explicitly includes the NOT NULL constraint for clarity.

Q28

Multiple Choice

Which SQL statement is used to enforce a unique primary key across two columns in the 'student_grades' table?

SQL Code

CREATE TABLE student_grades (
 student_id INT,
 subject_id INT,
 grade CHAR(2),
 PRIMARY KEY (student_id, subject_id)
);

CREATE TABLE student_grades ( student_id INT, subject_id INT, PRIMARY KEY (student_id) ); CREATE TABLE student_grades ( student_id INT, subject_id INT, PRIMARY KEY (subject_id) ); CREATE TABLE student_grades ( student_id INT, subject_id INT, PRIMARY KEY (student_id, subject_id) );CREATE TABLE student_grades ( student_id INT, subject_id INT, grade CHAR(2), PRIMARY KEY (student_id, subject_id) );

In MySQL, a composite primary key can be created using the PRIMARY KEY clause across two or more columns, ensuring that the combination of values is unique for each record.

Q29

Multiple Choice

Choose the SQL code that properly enforces referential integrity using a primary key and foreign key relationship.

SQL Code

CREATE TABLE departments (
 department_id INT AUTO_INCREMENT,
 department_name VARCHAR(100),
 PRIMARY KEY (department_id)
);
CREATE TABLE employees (
 employee_id INT AUTO_INCREMENT,
 employee_name VARCHAR(100),
 department_id INT,
 PRIMARY KEY (employee_id),
 FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE departments ( department_id INT AUTO_INCREMENT, department_name VARCHAR(100), PRIMARY KEY (department_id) ); CREATE TABLE employees ( employee_id INT AUTO_INCREMENT, employee_name VARCHAR(100), department_id INT, PRIMARY KEY (employee_id), FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id INT, employee_name VARCHAR(100), department_id INT, PRIMARY KEY (employee_id) );CREATE TABLE departments ( department_id INT AUTO_INCREMENT, PRIMARY KEY (department_id) );

Referential integrity in MySQL is maintained by linking two tables with a primary key in one table and a corresponding foreign key in another. This ensures consistency and accuracy of the data.

Q30

Multiple Choice

Which SQL code properly handles auto-incrementing a primary key for the 'projects' table?

SQL Code

CREATE TABLE projects (
 project_id INT AUTO_INCREMENT,
 project_name VARCHAR(100),
 start_date DATE,
 PRIMARY KEY (project_id)
);

CREATE TABLE projects ( project_id INT AUTO_INCREMENT, project_name VARCHAR(100), start_date DATE, PRIMARY KEY (project_id) ); CREATE TABLE projects ( project_id INT, project_name VARCHAR(100), start_date DATE, PRIMARY KEY (project_id) ); CREATE TABLE projects ( project_id INT AUTO_INCREMENT, project_name VARCHAR(100), PRIMARY KEY (project_id) );CREATE TABLE projects ( project_id INT, project_name VARCHAR(100), PRIMARY KEY (project_id) );

In MySQL, the AUTO_INCREMENT attribute is used with primary keys to automatically generate unique values for new records. This is essential for maintaining the integrity and uniqueness of the primary key column.

MySQL Database Quiz Questions