Candidate Key

A Candidate Key is a column, or a set of columns, in a table that can uniquely identify any database record without referring to any other data. Each table can have one or more candidate keys, but only one of them is chosen to be the primary key. The remaining candidate keys are often referred to as 'alternate keys'. The two main properties of a candidate key are that it must contain unique values for every row, and it must be a 'minimal' super key. A super key is any set of attributes that can uniquely identify a row. A candidate key is minimal because no attribute can be removed from it without losing the uniqueness property. For example, consider an 'Employees' table with columns 'employee_id', 'social_security_number' (ssn), 'email', and 'name'. In this table, 'employee_id' is unique for every employee. 'ssn' is also unique. 'email' is also likely to be unique. Therefore, {'employee_id'}, {'ssn'}, and {'email'} are all candidate keys. They are all candidates for being the primary key. The database designer would choose one of these (likely 'employee_id' as it's a simple integer and has no personal information) to be the primary key. 'ssn' and 'email' would then become alternate keys. The concept of candidate keys is important for understanding schema design and ensuring that there are multiple ways to uniquely identify records if needed, which can be useful for indexing and querying.