Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A database administrator is tasked with ensuring data integrity and consistency across multiple tables in an Azure SQL Database. Specifically, when a row in the 'Customers' table is deleted, all corresponding orders in the 'Orders' table must also be deleted. Which relational database concept should the administrator implement?
- AForeign Key
- BPrimary Key
- CView
- DIndex
Show answer & explanationAnswer & explanation
Correct answer: A. Foreign Key
A foreign key establishes a link between two tables, ensuring referential integrity. By defining a foreign key from 'Orders' to 'Customers' with a CASCADE DELETE action, deleting a customer will automatically delete all associated orders, maintaining data consistency.
Why the other options are wrong
- B. A primary key uniquely identifies rows within a single table but doesn't directly manage relationships or cascade deletions across tables.
- C. A view is a virtual table based on the result-set of a SQL query; it doesn't enforce data integrity rules or table relationships.
- D. An index improves query performance but does not enforce referential integrity or define deletion behavior between tables.
Foreign Key (Referential Integrity)
A column or set of columns in one table that refers to the primary key in another table, enforcing a link between the two tables.
- Establishes relationships between tables
- Enforces referential integrity
- Can define cascade actions (e.g., CASCADE DELETE)
Memory trick: Keys and constraints ensure data is always right.