School of Computing, Engineering and Information Sciences University of Northumbria

Candidate, Primary and Alternate Keys Aims: To apply the relational concept of candidate keys to the SQL equivalent of primary keys and alternate keys, and create tables in Oracle SQL with primary and alternate keys.

Outline of Session: Determine the candidate keys for the EMP, DEPT and PROJ tables already created. Add their SQL equivalents to the EMP, DEPT and PROJ tables. Create a new table ALLOC together with the Oracle SQL equivalent of its candidate key(s). Create the SQL equivalent of the CAR_OWNER and CAR_USER tables described in the lecture, complete with the equivalent of their candidate keys. Determine the candidate keys for the 2 club/society SQL tables already created, and add their SQL equivalents to the 2 tables.

School of Computing, Engineering and Information Sciences University of Northumbria

CANDIDATE KEYS In a relational database, each relation must have at least one Candidate Key, such that each Candidate Key(s) has a unique value(s) in each tuple at all times. Exercise: What Candidate Key(s) can you identify for the EMP table? and for the DEPT table? and for the PROJ table?

In SQL, for each table, a Candidate Key is selected to be its Primary Key, and specified as part of the CREATE TABLE statement. If there is only one Candidate Key in a table, then there is nothing more to be done. However, if there is more than one Candidate Key in a table, it is possible, and indeed desirable, to also specify them as part of the CREATE TABLE statement. In this case, the additional keys specified are called Alternate Keys, and a different syntax is used for them in SQL. If there is more than one Candidate Key, the choice of which one to select as the Primary Key is normally made by choosing the “smallest” Candidate Key to be the Primary Key, i.e. the one that consists of only one column, or the minimum number of columns. However other practical reasons for a choice, typically to make the table as easy to use as possible, are also used. SQL will ensure that a Primary Key value can never be NULL. However SQL does allow Alternate Key values to be NULL ! SQL only checks that the nonNULLs are unique. Thus to allow Alternate Keys to be always usable to identify a row in a table, the column(s) in them must also be made NON NULL. THE SQL SYNTAX FOR KEYS If a Primary Key consists of a single column, then it is usually specified as a “column constraint”, i.e. an integrity constraint that is placed immediately after the name and type of its column. Thus a column definition including a column constraint has the syntax : column-name [column constraint] The Primary Key column constraint itself has the syntax :

2

School of Computing, Engineering and Information Sciences University of Northumbria { CONSTRAINT constraint_name PRIMARY KEY |

PRIMARY KEY }

Thus the constraint consists of the keywords PRIMARY KEY, optionally prefixed by the keyword CONSTRAINT and a name for the constraint. If a Primary Key consists of 2 or more columns, then it must be specified as a “table constraint”, i.e. an integrity constraint that is placed after all the column definitions. A “column constraint” may also be specified as a “table constraint” if preferred. In general terms, this means the syntax of a CREATE TABLE statement is : CREATE TABLE table-name ( [ , etc.] [ , [ , etc.] ] ) ; This indicates that the column definitions may be followed by one or more table constraints, separated from the column definitions and from each other by commas. Both the column definitions and the table constraints appear within the round brackets/parentheses. The Primary Key table constraint has the syntax : { CONSTRAINT constraint_name PRIMARY KEY( column_name [, etc] ) | PRIMARY KEY( column_name [, etc] ) } The name(s) of all the column(s) forming the Primary Key must be put between the round brackets/parentheses. While in principle there is a choice of whether to give a Primary Key table constraint a name or not, in Oracle there is not. A name must always be given – so the second option is not viable on this course. In any case, it is preferable to give every integrity constraint, of every type, a name anyway, and this applies just as much to key constraints. If no name is given, then the DBMS automatically provides its own name; but if an error occurs involving the constraint, it is harder to find and use a DBMS-generated name than one of our own choosing. Therefore, to encourage good practice, we shall always give all our integrity constraints a name. It is helpful if we adopt a consistent naming convention. For Primary Keys, we shall use names of the form PKEY-underscore-tablename, e.g. PKEY_EMP for the EMP table; for Alternate Keys, we shall use names of the form AKEY-underscore-tablename, e.g. AKEY_EMP for the EMP table.

3

School of Computing, Engineering and Information Sciences University of Northumbria The syntax for Alternate Keys corresponds to that of Primary Keys. It can be summarised as follows. Column constraints : { CONSTRAINT constraint_name UNIQUE

|

UNIQUE }

Table constraints : { CONSTRAINT constraint_name UNIQUE( column_name [, etc] ) | UNIQUE( column_name [, etc] ) } Note that SQL uses the word UNIQUE for the Alternate Key ! All the column names involved in an Alternate Key must be made NON NULL. Do this by adding the NOT NULL constraint to all the columns’ definitions. When using column constraint syntax, put the NOT NULL before the Alternate Key constraint. ADDING KEYS TO SQL TABLES Add the Candidate Key constraints by dropping the existing tables from the database and then re-creating them with modified CREATE TABLE statements. For example, for the EMP table, you could do : SQL> DROP TABLE EMP; Table dropped. SQL> CREATE TABLE EMP ( 2 EMP_NO CHAR(2) CONSTRAINT PKEY_EMP PRIMARY KEY, 3 EMP_NAME CHAR(10), 4 SALARY INTEGER. 5 MARITAL_STATUS CHAR(1) 6 ); Table created.

EMP only has one Candidate Key. Now do the equivalent for the DEPT and PROJ tables. For any Alternate Keys, do not forget to ensure that they cannot accept NULLs !

4

School of Computing, Engineering and Information Sciences University of Northumbria

Exercise: For those table(s) with more than one Candidate Key, write down your justification for your choice of which was a Primary Key and which an Alternate key.

USE OF AN EDITOR When amending CREATE TABLE statements, it becomes tedious to re-enter the whole statement a second time, or even more frequently if errors arise. Therefore use the editor to store a CREATE TABLE statement in a file in your own personal storage directory. You can then call up the stored file and edit that instead. You can also run the stored file directly in Oracle SQL. The file is then known as an SQL “script file”. To run a script file, enter @path_name/file_name at the Oracle prompt. After the first such execution, Oracle remembers the path name and you need provide only the file name to execute the script. If you give such a file the extension .SQL, then you need not append the extension to the file name when you are executing the script.

TABLE “ALLOC” Here is an additional table that you should add to your database. Its purpose is to indicate which employees are working on which projects : Table Name ALLOC

Column Name EMP_NO PROJ_NO

Description A two-character code, not necessarily both digits. A two-character code, not necessarily both digits.

Data-type CHAR(2) CHAR(2)

Decide on its Candidate Key(s). Then create the equivalent SQL table with the corresponding PRIMARY KEY(S) and ALTERNATE KEY(S) (if any).

5

School of Computing, Engineering and Information Sciences University of Northumbria TABLES “CAR_OWNER” AND “CAR_USER” Refer to the lecture notes for details of both these tables. Draw up a description of what goes into each table, in the same format as is given above for table ALLOC (and previously for tables EMP, DEPT, and PROJ). Using the information provided in the lecture notes, decide on the Candidate Keys for each relation, and hence what would be the corresponding PRIMARY KEYS and ALTERNATE KEYS (if any) for the equivalent SQL tables. Then create the two tables accordingly. For any Alternate Keys, do not forget to ensure that they cannot accept NULLs !

CLUB / SOCIETY TABLES For the two tables previously created to help with the administration of a club or society, decide on their Candidate Keys, and hence what would be the corresponding PRIMARY KEYS and ALTERNATE KEYS (if any) for the equivalent SQL tables. Drop the original two tables and re-create them with the relevant keys.

6