A key need not consist of only one column, as it was used in the above example. It can consist of up to 127 columns which must not exceed a length of 255 characters, altogether.
Usually, keys are not constructed of more than five columns, because otherwise the user who must enter unique values would loose the overview.
Now let us see the slightly modified table 'customer'. First, the department where a particular customer is customer is to be recorded. Within such a department, the customers are numbered by hundreds; this will be checked using the modulo function.
CREATE TABLE customer (deptno FIXED (4) KEY
CONSTRAINT deptno BETWEEN 1 and 999,
cno FIXED (4) KEY
CONSTRAINT cno BETWEEN 1 AND 9999
AND cno MOD 100 = 0,
title ...
name ...
)
INSERT customer
VALUES (100, 3000, 'Mrs', ...)
INSERT customer
VALUES (100, 3100, 'Mr', ...)
INSERT customer
VALUES (100, 3200, 'Mr', ...)
INSERT customer
VALUES (200, 3000, 'Comp', ...)
INSERT customer
VALUES (200, 3000, 'Mrs', ...)
The entered values illustrate that neither the department number nor the customer number could guarantee uniqueness. But as a multiple key, these two numbers ensure that the customers are uniquely identified.
The order of the columns specified after the PRIMARY KEY clause defines the key sequence.
The direct access to a customer must be formulated in the following way:
SELECT DIRECT title, name
FROM customer
WHERE deptno = 100, cno = 3100