Data Definition Language (SQL DDL)

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(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.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,

DROP TABLE Product;
CREATE TABLE Product (
maker varchar(10), 
model varchar(50) PRIMARY KEY, 
type varchar(50)
);

We included the primary key specification into the column definition. However, we could achieve the same effect by using a separate constraint:

CONSTRAINT < constraint name > PRIMARY KEY (< list of COLUMNS making up the PRIMARY KEY >)

Our code will then look like this (again, we delete the previously created table first):

DROP TABLE Product;
CREATE TABLE Product (
maker varchar(10), 
model varchar(50), 
type varchar(50),
CONSTRAINT product_PK PRIMARY KEY (model)
);

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: :

If we correct the input error by providing a proper model code for the printer

INSERT INTO product VALUES
('A',    '1232',    'PC'),
('A',    '3001',    'Printer'),
(NULL,    NULL,    NULL);

The statement

INSERT INTO product VALUES
('A',    '1232',    'PC'),
('A',    '3001',    'Printer'),
(NULL,    '2000',    NULL);