SQL Keys
SQL Keys Interview with follow-up questions
Interview Question Index
- Question 1: What is a primary key in SQL and why is it important?
- Follow up 1 : Can a table have more than one primary key?
- Follow up 2 : What happens if the primary key has null values?
- Follow up 3 : How does a primary key differ from a unique key?
- Question 2: Can you explain what a foreign key is and provide an example of its use?
- Follow up 1 : What is referential integrity and how does a foreign key help maintain it?
- Follow up 2 : Can a foreign key accept null values?
- Follow up 3 : What happens if we try to delete a record that is being referred to by a foreign key?
- Question 3: What is a candidate key in SQL?
- Follow up 1 : Can a table have multiple candidate keys?
- Follow up 2 : How does a candidate key differ from a primary key?
- Follow up 3 : Can a candidate key have null values?
- Question 4: Can you explain the concept of a super key and how it differs from a primary key?
- Follow up 1 : Can a super key include non-essential attributes?
- Follow up 2 : Can a super key be a candidate key?
- Follow up 3 : Can a table have multiple super keys?
- Question 5: What is a surrogate key in SQL and why might you use one?
- Follow up 1 : What are the advantages and disadvantages of using a surrogate key?
- Follow up 2 : How does a surrogate key differ from a natural key?
- Follow up 3 : Can a surrogate key be a primary key?
Question 1: What is a primary key in SQL and why is it important?
Answer:
A primary key in SQL is a column or a set of columns that uniquely identifies each row in a table. It is important because it ensures the uniqueness and integrity of the data in the table. The primary key constraint enforces the uniqueness of the key values, meaning that no two rows can have the same primary key value. It also provides a way to efficiently retrieve and update specific rows in the table.
Follow up 1: Can a table have more than one primary key?
Answer:
No, a table cannot have more than one primary key. The primary key constraint is used to uniquely identify each row in a table, and having multiple primary keys would contradict this purpose. However, a primary key can consist of multiple columns, known as a composite primary key.
Follow up 2: What happens if the primary key has null values?
Answer:
A primary key cannot have null values. The primary key constraint requires that the key values be unique and not null. If a primary key column allows null values, it would not be able to uniquely identify each row in the table, which violates the primary key constraint.
Follow up 3: How does a primary key differ from a unique key?
Answer:
Both primary key and unique key constraints are used to enforce uniqueness in a table. However, there are some differences between them:
- A table can have only one primary key, but it can have multiple unique keys.
- The primary key constraint automatically creates a clustered index on the key column(s), whereas the unique key constraint does not.
- The primary key constraint does not allow null values, whereas the unique key constraint allows one null value (in case of a single-column unique key) or multiple null values (in case of a composite unique key).
Question 2: Can you explain what a foreign key is and provide an example of its use?
Answer:
A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a link between two tables by enforcing referential integrity. The foreign key constraint ensures that the values in the foreign key column(s) of one table match the values in the primary key column(s) of another table.
For example, consider two tables: 'orders' and 'customers'. The 'orders' table has a foreign key column 'customer_id' that references the primary key column 'id' in the 'customers' table. This relationship ensures that each order in the 'orders' table is associated with a valid customer in the 'customers' table.
Follow up 1: What is referential integrity and how does a foreign key help maintain it?
Answer:
Referential integrity is a database concept that ensures the relationships between tables are maintained correctly. It ensures that the values in the foreign key column(s) of a table match the values in the primary key column(s) of the referenced table.
A foreign key helps maintain referential integrity by enforcing the following rules:
Insert Rule: It ensures that a value cannot be inserted into the foreign key column(s) unless it matches a value in the primary key column(s) of the referenced table.
Update Rule: It ensures that if a value in the primary key column(s) of the referenced table is updated, the corresponding foreign key values in other tables are also updated.
Delete Rule: It ensures that if a record in the referenced table is deleted, the corresponding records in other tables that reference it through foreign keys are also deleted or updated accordingly.
Follow up 2: Can a foreign key accept null values?
Answer:
Yes, a foreign key can accept null values. By default, a foreign key column allows null values, which means it can have missing or unknown values. However, it is generally recommended to avoid using null values in foreign key columns as they can lead to data integrity issues. It is best to define the foreign key column as NOT NULL to enforce the relationship between tables.
Follow up 3: What happens if we try to delete a record that is being referred to by a foreign key?
Answer:
If you try to delete a record that is being referred to by a foreign key, the database management system (DBMS) will prevent the deletion to maintain referential integrity. The specific behavior depends on the delete rule specified for the foreign key constraint.
There are typically three delete rules:
CASCADE: If the delete rule is set to CASCADE, deleting a record in the referenced table will automatically delete the corresponding records in other tables that reference it through foreign keys.
SET NULL: If the delete rule is set to SET NULL, deleting a record in the referenced table will set the foreign key values in other tables to NULL.
SET DEFAULT: If the delete rule is set to SET DEFAULT, deleting a record in the referenced table will set the foreign key values in other tables to their default values.
It is important to choose the appropriate delete rule based on the desired behavior and data integrity requirements.
Question 3: What is a candidate key in SQL?
Answer:
A candidate key in SQL is a column or a set of columns that uniquely identifies each row in a table. It is a potential primary key candidate for the table.
Follow up 1: Can a table have multiple candidate keys?
Answer:
Yes, a table can have multiple candidate keys. Each candidate key represents a different way to uniquely identify each row in the table. However, only one candidate key can be selected as the primary key.
Follow up 2: How does a candidate key differ from a primary key?
Answer:
A candidate key is a potential primary key candidate, while a primary key is the chosen candidate key that is used to uniquely identify each row in a table. Only one candidate key can be selected as the primary key.
Follow up 3: Can a candidate key have null values?
Answer:
No, a candidate key cannot have null values. A candidate key must have unique and non-null values for each row in a table.
Question 4: Can you explain the concept of a super key and how it differs from a primary key?
Answer:
A super key is a set of one or more attributes that can uniquely identify a tuple (row) in a relation (table). It is a broader concept than a primary key. A super key may contain additional attributes that are not necessary for uniquely identifying a tuple. On the other hand, a primary key is a minimal super key, meaning it is a super key with no unnecessary attributes. In other words, a primary key is a super key that uniquely identifies a tuple and does not contain any redundant attributes.
Follow up 1: Can a super key include non-essential attributes?
Answer:
Yes, a super key can include non-essential attributes. A super key is a set of attributes that can uniquely identify a tuple, but it may also contain additional attributes that are not necessary for uniqueness. These non-essential attributes are called redundant attributes. However, a primary key, which is a special type of super key, cannot include non-essential attributes.
Follow up 2: Can a super key be a candidate key?
Answer:
Yes, a super key can be a candidate key. A candidate key is a minimal super key, meaning it is a super key with no unnecessary attributes. Since a super key can include additional attributes that are not necessary for uniqueness, it is possible for a super key to be a candidate key if it does not contain any redundant attributes.
Follow up 3: Can a table have multiple super keys?
Answer:
Yes, a table can have multiple super keys. Each super key is a set of attributes that can uniquely identify a tuple in the table. Different combinations of attributes can form different super keys. However, it is important to note that a table can have only one primary key, which is a special type of super key that is chosen as the main identifier for the table.
Question 5: What is a surrogate key in SQL and why might you use one?
Answer:
A surrogate key is a unique identifier that is added to a table in a database to uniquely identify each record. It is typically an artificially generated value, such as an auto-incrementing integer or a globally unique identifier (GUID). Surrogate keys are used when there is no natural key or when the natural key is not suitable for use as a primary key. They provide a simple and efficient way to uniquely identify records and are often used in database systems to improve performance and simplify data management.
Follow up 1: What are the advantages and disadvantages of using a surrogate key?
Answer:
Advantages of using a surrogate key include:
- Uniqueness: Surrogate keys ensure that each record in a table has a unique identifier, regardless of the data contained in the record.
- Simplified data management: Surrogate keys provide a simple and consistent way to identify records, making it easier to manage and manipulate data.
- Performance: Surrogate keys can improve performance in certain scenarios, such as when joining tables or indexing data.
Disadvantages of using a surrogate key include:
- Increased storage requirements: Surrogate keys add an additional column to each table, which can increase storage requirements.
- Complexity: Surrogate keys can add complexity to the database design and may require additional logic to handle.
- Lack of meaning: Surrogate keys do not have any inherent meaning or significance, which can make it harder to understand the data without additional context.
Follow up 2: How does a surrogate key differ from a natural key?
Answer:
A surrogate key is an artificially generated identifier that is added to a table to uniquely identify each record. It does not have any inherent meaning or significance and is typically a numeric or alphanumeric value. In contrast, a natural key is a column or set of columns in a table that has a meaningful and unique value, such as a person's social security number or a product's SKU. Natural keys are derived from the data itself and have a direct relationship to the real-world entities being represented in the database. Surrogate keys are often used when there is no suitable natural key or when the natural key is not ideal for use as a primary key.
Follow up 3: Can a surrogate key be a primary key?
Answer:
Yes, a surrogate key can be used as a primary key in a database table. In fact, one of the main reasons for using a surrogate key is to serve as the primary key for a table. Surrogate keys provide a simple and efficient way to uniquely identify records, and they can be easily managed and manipulated by the database system. However, it is important to note that a surrogate key is not the only option for a primary key, and in some cases, a natural key may be more appropriate.