In SQL, what is the definition of a key?
With so many different types of keys (foreign, primary, unique, natural, super, etc), it can really get quite confusing to have a solid understanding of keys in SQL. And to make it even more confusing, many people think that relational database theory (which deals with terms like tuples, attributes, and relations), SQL (which deals with terms like tables rows and columns, and is our main concern here), and file systems (which deal with terms like records and fields) are all the same concept when in fact they are all completely different.
With that in mind, we want to give the proper definition of keys in SQL so that the foundation of your understanding can be solid.
The Definition of A Key in SQL
According to the SQL Standard, a key is a subset of columns in a table that allow a row to be uniquely identified. So, a key can be more than just one column. And, every row in the table will have a unique value for the key – or a unique combination of values if the key consists of more than just one column.
Can a key have NULL values in SQL?
According to the SQL standard, a key is not allowed to have values that are NULL-able. Any key that has more columns than necessary to uniquely identify each row in the table is called a super-key (think of it as a super-set). But, if the key has the minimum amount of columns necessary to uniquely identify each row then it is called a minimal super-key. A minimal super-key is also known as a candidate key, and there must be one or more candidate keys in a table.
Keys in actual RDBMS implementations
Even though the SQL standard says that a key can not be NULL, in practice actual RDBMS implementations (like SQL Server and Oracle), allow both foreign and unique keys to actually be NULL. And there are plenty of times when that actually makes sense. However, a primary key can never be NULL.