Entity Integrity page 1
Entity integrity means that each object represented by a row in the table should be distinguishable from any other object. In other words, there should be such a set of attributes whose unique combination of values would allow telling one object from another. Such an attribute set unambiguously identifying the object is called a candidate key. It can’t contain NULL marks because it won’t let us identify the object (like, say, a book with an unknown title).
A table can have several candidate keys. For instance, a person can be identified by her/his passport number, insurance policy number, VATIN, driver’s license number, etc.
To enforce entity integrity, SQL lets you define the PRIMARY KEY and UNIQUE specifications. A table can only have one primary key, but several unique indexes. That is, you can define the PRIMARY KEY specification for one of the candidate keys, and UNIQUE for the rest of them.
What can serve as the primary key in our case? Since a maker can produce several models and thus turn up in the Product table data several times, the maker column isn’t suitable for the role of the primary key. Similarly, the type attribute isn’t unique either.
The only attribute having no duplicates is the model code, and this is our sole contender to be the primary key. There are no other candidate keys in the table. To proof that, we can scrutinize all other column combinations and demonstrate they don’t ensure object identification. In particular, the value combination of the three columns in the example above is unique, yet fails to identify model 1232 nevertheless.
Let’s create a primary key. SQL allows changing the structure of an existing table by means of the ALTER TABLE statement. However, to avoid too much information at one time, we’ll just re-create the table, that is, delete it and create it anew with a primary key. Deleting a table isn’t difficult (destroying is easy, building is hard, as the saying goes) – you just need to carry out the DROP TABLE <table name> statement. So then,
We included the primary key specification into the column definition. However, we could achieve the same effect by using a separate constraint:
Our code will then look like this (again, we delete the previously created table first):
Now, the DBMS itself will make sure the primary key doesn’t have any duplicate values or NULL marks. If we try to add data using the INSERT statement from before, we’ll get the following error message: :
Violation of PRIMARY KEY constraint 'product_PK'. Cannot insert duplicate key in object 'Product'. The duplicate key value is (1232).
If we correct the input error by providing a proper model code for the printer
Cannot insert the value NULL into column 'model', table 'Product'; column does not allow nulls. INSERT fails.