Database Reference
In-Depth Information
Figure 4.7
References must remain consistent.
row 2 of the weblog table still refers to row 2 of the cookie table which we have just
removed. If we tried to join the two tables together to get back all of the information stored
between them, the system would not be able to find the reference to the Mozilla cookie, so
the join would fail. Removal of row 2 from the cookie table has compromised the referen-
tial integrity of the rest of the database. The only way that we could remove row 2 of the
cookie table is if we removed every row that contained a reference to it in all related tables.
We have to remove all of the foreign key references to this row first, and then remove the
primary key from the cookie table. There is a specific type of command used for deleting
data while maintaining referential integrity called a CASCADE delete. This will be touched
upon in a later chapter.
Most DBMS will try to maintain and enforce referential integrity, but this will only work
if you have correctly told the system where the relationships between tables are. If you are
using a scripting language such as PHP, it will be possible to enforce referential integrity in
your code, and not with the database. However, this will only work if your code works, and
so it is much better to let the DBMS enforce the rules.
MySQL will only enforce referential integrity if you are using a specific type of table. The
default table on most installations will be of the MyISAM type. This type does not enforce
referential integrity. If you are creating tables which need referential integrity enforced, you
will need to create InnoDB tables. The online MySQL manual contains a section on ensur-
ing that your MySQL server supports these tables.
NULL
Finally, before I show you how to create a table, we need to deal with NULL. A NULL entry
is placed in a column when there is no value stored in that column. The NULL is a special
symbol which signifies that no value has been inserted yet. A NULL is not like the empty
string (“”), because the empty string is a string with no contents. NULL is the absence of
content. Because of this you cannot match a NULL in comparisons.
When creating a table, you can specify that a column cannot contain a NULL by using
NOT NULL after the column type. If you are specifying a column that is a foreign key, the
Search WWH ::
Custom Search