Data Definition Language (SQL DDL)

As you probably have noticed, the error message about the violation of the primary key constraint refers to the constraint name ('product_PK'). But what happens if no name is specified? This has been the case in our first table definition, as we included the PRIMARY KEY specification into the model column definition. By the way, it’s also possible not to specify the constraint name in the second table definition (then, the CONSTRAINT keyword has to be omitted, as well):

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

Why is it so important for us to know the constraint name? In addition to being helpful in finding out the cause of an error, this name is required for removing the corresponding constraint when we change the structure of an existing table. If we don’t specify a constraint name, the DBMS assigns one on its own. And we can learn this name, which must be unique within the scope of the database, from the information schema view – the standard method for obtaining metadata. E. g., the primary key constraint name created by the last script can be retrieved from a table in the information schema view using a regular query:

SELECT CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME='Product' AND CONSTRAINT_TYPE ='primary key';

On my system, the constraint name is PK__Product__0B7E269E30F848ED. In all likelihood, you will get a different name, because it is generated by the DBMS.

An example of a compound primary key is the primary key in the Outcomes table (Ships database). Here, only the pair of columns {ship, battle} can be unique, because a ship can take part in more than one battle, and there are several ships participating in a battle. However, the same ship can’t be mentioned in a specific battle more than one time, and that’s exactly what the primary key will forbid to do. The Outcomes table with the aforementioned primary key can be created as follows:

CREATE TABLE Outcomes (
ship varchar(50),
battle varchar(20),
result varchar(10),
PRIMARY KEY(ship, battle)
);

It should be mentioned that the following notation

CREATE TABLE Outcomes (
ship varchar(50) PRIMARY KEY,
battle varchar(20) PRIMARY KEY,
result varchar(10)
);

If a table can have only one primary key, what should we do when our data model requires several attribute combinations not to have duplicate values? In other words, how do we create alternative keys if, e. g., we need to add a column named out_id with unique values to the Outcomes table?

For this purpose,  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 provides the UNIQUE specification. The statement for creating the Outcomes table with the additional column named out_id could look like this: 

CREATE TABLE Outcomes (
ship varchar(50),
battle varchar(20),
result varchar(10),
out_id int,
PRIMARY KEY(ship, battle),
UNIQUE (out_id)
);

As mentioned before, several UNIQUE constraints can be defined for a table.

There is another aspect this constraint differs from PRIMARY KEY. The column UNIQUE is applied to can contain a NULL mark, but only one. You may ask – what about object identification in this case? Let’s recall the example with the books being sought for by unique title. Since only one NULL mark is allowed to exist, we can find the book with the unspecified title by eliminating all books with known titles.

Should we need an alternative key not allowing NULL marks, we can achieve this by applying, in addition to UNIQUE, the NOT NULL constraint. That’s what we’re now passing on to.