Which of the following can best enforce the referential integrity of the relational database?
A. Primary key
B. Foreign key
C. Candidate key
D. View

Kindly be reminded that the suggested answer is for your reference only. It doesn’t matter whether you have the right or wrong answer. What really matters is your reasoning process and justifications.

My suggested answer is B. Foreign key.

• Foreign key enforces referential integrity.
• Primary key enforces entity integrity.
• Candidate keys uniquely identify a tuple (record) in a relation (table) as the primary key does, but it is not selected as the primary key.
• A view is a virtual table, served as an access control mechanism that joins one or more tables and selects fields of interest.

Database Integrity

Database integrity can be enforced through physically implementing reliable storage, operationally controlling concurrency, and semantically applying controls through declaratively specifying integrity constraints and procedurally defining triggers and units of work as transactions.

For information to have integrity, its “soundness” needs to be above questioning. The information should be complete (it should be the whole truth) and valid (nothing but the whole truth). Leyman and Roller identify three types of integrity:
• Physical integrity protects against the loss of data due to problems such as media failures and magnetic interference;
• Operational integrity is concerned with the synchronization of concurrent access to the data;
• Semantic integrity ensures that the data retains its meaning, i.e. that the data correctly reflects the real world that it models.

Source: Reinhardt A. Botha

Semantic Integrity

There are three common types of semantic integrity:

• Entity integrity requires that each entity have a unique key. (IBM)
• Referential integrity is the state in which all values of all foreign keys are valid. (IBM)
• Domain integrity specifies that all columns in a relational database must be declared upon a defined domain. (Wikipedia)

Integrity Constraints

According to Can Turker and Michael Gertz, SQL-99 provides the following keywords for declaratively specifying integrity constraints:

• PRIMARY KEY specifies the primary key of a table
• FOREIGN KEY (REFERENCES) defines a foreign key whose values must match to the values of a unique/primary key
• NOT NULL prevents a column from taking the null value
• DEFAULT sets the default value of a column
• UNIQUE fines that a column or set of columns must have unique values within a table
• CHECK defines a general integrity constraint that must hold for each row of a table,
• DOMAIN creates a (restricted) column domain, and
• ASSERTION defines a named, general integrity constraint that may refer to more than one table.

Reference

• TOWARDS SEMANTIC INTEGRITY IN RELATIONAL DATABASES
• Semantic Integrity Support in SQL-99 and Commercial (Object-)Relational Database Management Systems
• Entity integrity, referential integrity and referential constraints

A BLUEPRINT FOR YOUR SUCCESS IN CISSP

My new book, The Effective CISSP: Security and Risk Management, helps CISSP aspirants build a solid conceptual security model. It is not only a tutorial for information security but also a study guide for the CISSP exam and an informative reference for security professionals.

• It is available on Amazon.
• Readers from countries or regions not supported by Amazon can get your copy from the author’s web site.

以下哪項可以最能維持關聯式資料庫的參照完整性？
A. Primary key
B. Foreign key
C. Candidate key
D. View