Test Your MySQL Surrogate Key Knowledge with Our SQL Quiz

Progress

True / False

A surrogate key in MySQL is an artificial key that is used as a unique identifier for each record in a table.

True False

A surrogate key is indeed an artificial key, often an auto-incrementing integer, that uniquely identifies each record in a table.

True / False

Surrogate keys in MySQL can be based on business logic.

True False

Surrogate keys are artificial and not derived from business logic. They are typically auto-incrementing numbers.

True / False

A surrogate key in MySQL can be a combination of multiple columns.

True False

A surrogate key is usually a single column with unique, system-generated values, such as an auto-incrementing integer.

True / False

In MySQL, surrogate keys can improve query performance by serving as primary keys.

True False

Surrogate keys, when used as primary keys, can improve query performance by providing a unique, indexed identifier for each row.

True / False

A surrogate key in MySQL must always be an integer.

True False

While surrogate keys are commonly integers, they can also be UUIDs or other unique, system-generated values.

True / False

Surrogate keys in MySQL are ideal for maintaining relationships between tables in a database.

True False

Surrogate keys are often used to maintain relationships between tables because they are unique and do not change.

True / False

Using a surrogate key in MySQL guarantees that the values will never have gaps (i.e., they will be consecutive).

True False

Surrogate keys, especially auto-incrementing integers, can have gaps if rows are deleted or transactions are rolled back.

True / False

Surrogate keys in MySQL eliminate the need for natural keys in database design.

True False

Surrogate keys do not eliminate the need for natural keys; they simply provide a unique identifier. Natural keys are still important for ensuring data integrity and uniqueness based on real-world data.

True / False

Surrogate keys in MySQL can be indexed for faster lookups and joins.

True False

Surrogate keys can be indexed, which improves the performance of lookups and joins by providing a fast way to locate rows based on the unique identifier.

Q10

True / False

In MySQL, it is possible to create a surrogate key using the AUTO_INCREMENT attribute.

True False

The AUTO_INCREMENT attribute in MySQL is commonly used to create surrogate keys, as it automatically generates a unique integer value for each new record.

Q11

Single Choice

What is a surrogate key in MySQL?

A key that uniquely identifies each row in a table A column that has no intrinsic meaning and is used solely for identifying rows A key that is a natural attribute of the entityA foreign key in a table

A surrogate key is an artificial key that is used to uniquely identify rows in a table. It does not have any business meaning and is typically an auto-incremented integer.

Q12

Single Choice

Which MySQL data type is commonly used for a surrogate key?

VARCHAR TEXT INTDATE

The INT data type is commonly used for surrogate keys because it can auto-increment, providing a unique identifier for each row.

Q13

Single Choice

In MySQL, how do you set a column as a surrogate key?

CREATE TABLE table_name (id VARCHAR PRIMARY KEY); CREATE TABLE table_name (id INT PRIMARY KEY); CREATE TABLE table_name (id INT AUTO_INCREMENT PRIMARY KEY);CREATE TABLE table_name (id DATE PRIMARY KEY);

Using AUTO_INCREMENT along with PRIMARY KEY ensures that the column will automatically generate unique values for each row.

Q14

Single Choice

What is a key advantage of using a surrogate key in MySQL?

They are always smaller in size compared to natural keys They ensure uniqueness and are not affected by changes in business logic They improve the readability of the dataThey are easier to remember

Surrogate keys are independent of business logic and remain unique, even if the business rules or data values change.

Q15

Single Choice

How does MySQL handle surrogate key collisions when using AUTO_INCREMENT?

It will generate a random new key It will throw an error and stop the insertion It will skip the duplicate value and continueIt automatically adjusts the AUTO_INCREMENT value to avoid collision

MySQL adjusts the AUTO_INCREMENT value to ensure that each inserted row gets a unique value, preventing collisions.

Q16

Single Choice

What is a potential drawback of using surrogate keys in MySQL?

They are always slower than natural keys They can cause issues with foreign key relationships They do not provide any business contextThey require more storage space than natural keys

Surrogate keys do not carry any business meaning, which can make understanding and debugging the data more challenging.

Q17

Single Choice

In a MySQL database with heavy write operations, what is a recommended approach to handle surrogate keys?

Use VARCHAR data type for surrogate keys Use composite keys instead of surrogate keys Use BIGINT for surrogate keys if INT range might be exceededAvoid using surrogate keys altogether

For tables with heavy write operations and a large number of rows, using BIGINT can prevent issues when the INT range is exhausted.

Q18

Single Choice

Which MySQL feature allows for automatic generation of surrogate keys without specifying AUTO_INCREMENT?

Triggers Sequences Stored proceduresDefault constraints

Triggers can be used to automatically generate surrogate keys based on complex logic or conditions without relying solely on AUTO_INCREMENT.

Q19

Single Choice

In a distributed MySQL database environment, what is a strategy to ensure unique surrogate keys across different nodes?

Use a composite key with a node identifier Rely on AUTO_INCREMENT without any changes Use UUIDs as surrogate keysManually assign keys to each row

UUIDs (Universally Unique Identifiers) ensure unique surrogate keys across distributed systems and avoid conflicts between nodes.

Q20

Single Choice

Which MySQL statement correctly creates a table with a surrogate key and ensures that the key values are unique and automatically incremented?

CREATE TABLE employees (emp_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255)) CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(255)); CREATE TABLE employees (emp_id INT UNIQUE AUTO_INCREMENT, name VARCHAR(255));CREATE TABLE employees (emp_id INT AUTO_INCREMENT, name VARCHAR(255));

This statement correctly sets emp_id as the primary key and uses AUTO_INCREMENT to ensure that it is unique and automatically incremented for each new row.

Q21

Multiple Choice

Which SQL statement demonstrates the creation of a surrogate key in a table design?

SQL Code

CREATE TABLE employees (
 employee_id INT AUTO_INCREMENT PRIMARY KEY,
 employee_name VARCHAR(100)
);

CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100) ); CREATE TABLE employees ( employee_id INT AUTO_INCREMENT PRIMARY KEY, employee_name VARCHAR(100) ); CREATE TABLE employees ( employee_name VARCHAR(100) );CREATE TABLE employees ( employee_id INT, employee_name VARCHAR(100) );

A surrogate key is a unique identifier for each record in a table, typically an integer that is automatically generated by the database. In this example, 'employee_id' is a surrogate key.

Q22

Multiple Choice

Which SQL code snippet correctly defines a surrogate key in a 'products' table?

SQL Code

CREATE TABLE products (
 product_id INT AUTO_INCREMENT PRIMARY KEY,
 product_name VARCHAR(100)
);

CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100) ); CREATE TABLE products ( product_id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(100) ); CREATE TABLE products ( product_name VARCHAR(100) );CREATE TABLE products ( product_id INT, product_name VARCHAR(100) );

A surrogate key is an artificial key that is automatically generated, often used instead of a natural key. In this example, 'product_id' is a surrogate key created using AUTO_INCREMENT.

Q23

Multiple Choice

Which SQL statement demonstrates the use of a surrogate key and a natural key in the same table?

SQL Code

CREATE TABLE orders (
 order_id INT AUTO_INCREMENT PRIMARY KEY,
 order_number VARCHAR(20) UNIQUE,
 customer_id INT
);

CREATE TABLE orders ( order_id INT PRIMARY KEY, order_number VARCHAR(20) ); CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, order_number VARCHAR(20) UNIQUE, customer_id INT ); CREATE TABLE orders ( order_number VARCHAR(20) PRIMARY KEY, customer_id INT );CREATE TABLE orders ( order_id INT, order_number VARCHAR(20) );

In this example, 'order_id' is a surrogate key, while 'order_number' is a natural key. The surrogate key is generated automatically and serves as the primary key, while the natural key uniquely identifies orders.

Q24

Multiple Choice

Which SQL code snippet demonstrates the proper implementation of a surrogate key in a 'departments' table?

SQL Code

CREATE TABLE departments (
 department_id INT AUTO_INCREMENT PRIMARY KEY,
 department_name VARCHAR(100)
);

CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(100) ); CREATE TABLE departments ( department_id INT AUTO_INCREMENT PRIMARY KEY, department_name VARCHAR(100) ); CREATE TABLE departments ( department_name VARCHAR(100) );CREATE TABLE departments ( department_id INT, department_name VARCHAR(100) );

The 'department_id' in this example is a surrogate key generated by the database, ensuring that each department has a unique identifier, which is not derived from the actual data.

Q25

Multiple Choice

Which SQL code correctly uses a surrogate key in a 'customers' table design?

SQL Code

CREATE TABLE customers (
 customer_id INT AUTO_INCREMENT PRIMARY KEY,
 customer_name VARCHAR(100),
 email VARCHAR(100)
);

CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100) ); CREATE TABLE customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(100), email VARCHAR(100) ); CREATE TABLE customers ( customer_name VARCHAR(100) );CREATE TABLE customers ( customer_id INT, customer_name VARCHAR(100) );

A surrogate key is an artificial key that the database generates. The 'customer_id' in this table is a surrogate key, automatically incremented for each new record.

Q26

Multiple Choice

Which SQL code snippet demonstrates the use of both a surrogate key and a natural key in a 'students' table?

SQL Code

CREATE TABLE students (
 student_id INT AUTO_INCREMENT PRIMARY KEY,
 student_number VARCHAR(10) UNIQUE,
 student_name VARCHAR(100)
);

CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(100) ); CREATE TABLE students ( student_number VARCHAR(10) PRIMARY KEY, student_name VARCHAR(100) ); CREATE TABLE students ( student_id INT AUTO_INCREMENT PRIMARY KEY, student_number VARCHAR(10) UNIQUE, student_name VARCHAR(100) );CREATE TABLE students ( student_id INT, student_number VARCHAR(10) );

In this table, 'student_id' is a surrogate key automatically generated by the database, while 'student_number' is a natural key that uniquely identifies each student.

Q27

Multiple Choice

Which SQL code correctly illustrates the use of a surrogate key in a 'courses' table?

SQL Code

CREATE TABLE courses (
 course_id INT AUTO_INCREMENT PRIMARY KEY,
 course_name VARCHAR(100)
);

CREATE TABLE courses ( course_id INT PRIMARY KEY, course_name VARCHAR(100) ); CREATE TABLE courses ( course_id INT AUTO_INCREMENT PRIMARY KEY, course_name VARCHAR(100) ); CREATE TABLE courses ( course_name VARCHAR(100) );CREATE TABLE courses ( course_id INT, course_name VARCHAR(100) );

A surrogate key is a unique identifier that the database generates automatically. The 'course_id' in this table is an example of a surrogate key, ensuring each course has a unique identifier.

Q28

Multiple Choice

Which SQL statement best demonstrates a surrogate key implementation in a 'projects' table?

SQL Code

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

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

In this table, 'project_id' is a surrogate key generated by the database. Surrogate keys are typically numeric and automatically incremented to ensure each record has a unique identifier.

Q29

Multiple Choice

Which SQL code snippet best illustrates the use of a surrogate key in a 'suppliers' table design?

SQL Code

CREATE TABLE suppliers (
 supplier_id INT AUTO_INCREMENT PRIMARY KEY,
 supplier_name VARCHAR(100),
 contact_email VARCHAR(100)
);

CREATE TABLE suppliers ( supplier_id INT PRIMARY KEY, supplier_name VARCHAR(100) ); CREATE TABLE suppliers ( supplier_id INT AUTO_INCREMENT PRIMARY KEY, supplier_name VARCHAR(100), contact_email VARCHAR(100) ); CREATE TABLE suppliers ( supplier_name VARCHAR(100) );CREATE TABLE suppliers ( supplier_id INT, supplier_name VARCHAR(100) );

The 'supplier_id' in this table is a surrogate key, automatically generated by the database to uniquely identify each supplier. This ensures each record has a unique identifier, independent of the actual data.

Q30

Multiple Choice

Which SQL statement demonstrates the correct use of a surrogate key in a 'locations' table?

SQL Code

CREATE TABLE locations (
 location_id INT AUTO_INCREMENT PRIMARY KEY,
 location_name VARCHAR(100),
 address VARCHAR(255)
);

CREATE TABLE locations ( location_id INT PRIMARY KEY, location_name VARCHAR(100) ); CREATE TABLE locations ( location_id INT AUTO_INCREMENT PRIMARY KEY, location_name VARCHAR(100), address VARCHAR(255) ); CREATE TABLE locations ( location_name VARCHAR(100) );CREATE TABLE locations ( location_id INT, location_name VARCHAR(100) );

In this table, 'location_id' is a surrogate key, ensuring that each location has a unique, automatically generated identifier. Surrogate keys are commonly used when no natural key is present.

MySQL Database Quiz Questions