Database Reference
In-Depth Information
The following command will enable user Jones to retrieve data from the Customer table but not take any
other action.
GRANT SELECT ON Customer TO Jones
;
The following command will enable users Smith and Park to add new records to the Part table.
131
GRANT INSERT ON Part TO Smith, Park
;
The following command will revoke the ability to retrieve Customer records from user Jones; that is,
Jones will no longer have the privilege granted earlier.
REVOKE SELECT ON Customer FROM Jones
;
INTEGRITY RULES
A relational DBMS must enforce two important integrity rules that were defined by Dr. E. F. Codd (Codd, E.
F.
In ACM TODS 4, no. 4 [December
1979]). Both rules are related to two special types of keys: primary keys and foreign keys. The two integrity
rules are called entity integrity and referential integrity.
“
Extending the Relational Database Model to Capture More Meaning.
”
Entity Integrity
In some DBMSs, when you describe a database, you can indicate that certain fields can accept a special value,
called null. Essentially, setting the value in a given field to null is similar to not entering a value in the field at
all. Nulls are used when a value is missing, unknown, or inapplicable. It is not the same as a blank or zero
value, both of which are actual values. For example, a value of zero in the Balance field for a particular cus-
tomer indicates that the customer has a zero balance. A value of null in a customer
'
s Balance field, on the
other hand, indicates that, for whatever reason, the customer
s balance is unknown.
When you indicate that the Balance field can be null, you are saying that this situation (a customer with
an unknown balance) is something you want to allow. If you do not want to allow unknown values, you indi-
cate this by specifying that Balance field values cannot be null.
The decision about allowing nulls is generally made on a field-by-field basis. There is one type of field for
which you should never allow nulls, however, and that is the primary key. After all, the primary key is sup-
posed to uniquely identify a given row, which would not happen if nulls were allowed. How, for example,
could you tell two customers apart if both had null customer numbers? The restriction that the primary key
cannot allow null values is called entity integrity.
'
Definition:
Entity integrity is the rule that no field that is part of the primary key may accept null values.
Entity integrity guarantees that each record will indeed have its own identity. In other words, preventing
the primary key from accepting null values ensures that you can distinguish one record from another. Typi-
cally, the DBMS handles this distinction automatically. All you need to do is specify which field or fields make
up the primary key.
In SQL, you can specify the primary key by entering a PRIMARY KEY clause in either an ALTER TABLE
(covered later in this chapter) or a CREATE TABLE command. For example, to use the PRIMARY KEY clause
to indicate that CustomerNum is the primary key for the Customer table, the clause would be as follows:
PRIMARY KEY (CustomerNum)
In general, the PRIMARY KEY clause has the form PRIMARY KEY followed, in parentheses, by the field or
fields that make up the primary key. When more than one field is included, the fields are separated by com-
mas. Thus, the PRIMARY KEY clause for the OrderLine table is as follows:
PRIMARY KEY (OrderNum, PartNum)
In Access, you designate the primary key by selecting the primary key field in Table Design view and
clicking the Primary Key button in the Tools group on the Table Tools Design tab. A key symbol will appear
in the field
'
s row selector to indicate that it is the primary key, as shown in Figure 4-15.
