MySQL in a Nutshell [53]
ALTER TABLE squares
ADD SPATIAL INDEX square_index (square_points);
SHOW INDEXES FROM squares \G
*************************** 1. row ***************************
Table: squares
Non_unique: 1
Key_name: square_index
Seq_in_index: 1
Column_name: square_points
Collation: A
Cardinality: NULL
Sub_part: 32
Packed: NULL
Null:
Index_type: SPATIAL
Comment:
Notice that when we created the table, we specified that the column square_points is NOT NULL. This is required to be able to index the column. See the CREATE INDEX statement for SPATIAL indexes in this chapter for an explanation and more examples related to spatial indexes.
ALTER TABLE: ADD clauses for foreign keys
ALTER [IGNORE] TABLE table
ADD [CONSTRAINT [symbol]] PRIMARY KEY [USING index_type] (column,...) |
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] index [USING index_type]
(column,...) |
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...)
[REFERENCES table (column,...)
[ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]
[ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]]
These ADD clauses add foreign keys and references to InnoDB tables. A foreign key is an index that refers to a key or an index in another table. See the explanation of the CREATE TABLE statement later in this chapter for more information and for an example of an SQL statement involving the creation of foreign keys in a table. The various flags shown are also explained in the CREATE TABLE statement.
Here is an example:
CREATE TABLE employees
(emp_id INT AUTO_INCREMENT PRIMARY KEY,
tax_id CHAR(12),
emp_name VARCHAR(100))
ENGINE = INNODB;
CREATE TABLE employees_telephone
(emp_id INT,
tel_type ENUM('office','home','mobile'),
tel_number CHAR(25))
ENGINE = INNODB;
ALTER TABLE employees_telephone
ADD FOREIGN KEY emp_tel (emp_id)
REFERENCES employees (emp_id)
ON DELETE RESTRICT;
The first two SQL statements create InnoDB tables: one for basic employee information and the other for employee telephone numbers. Using the ALTER TABLE statement afterward, we add a foreign key restriction between the two. Let’s look at the results using the SHOW TABLE STATUS statement, because the SHOW INDEXES statement won’t show foreign key restraints:
SHOW TABLE STATUS FROM human_resources
LIKE 'employees_telephone' \G
*************************** 1. row ***************************
Name: employees_telephone
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: NULL
Create_time: 2007-04-03 04:01:39
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 4096 kB; ('emp_id')
REFER 'human_resources'.'employees'('emp_id')
In the Comment field, we can see that we’ve created a restraint on the main table employees from employees_telephone. We’re telling MySQL not to allow a row for an employee to be removed from the employees table without first removing the rows of data for the employee in the employees_telephone table.
In the following example, we first insert an employee in the employees table, then add her home telephone number to the second table, and then attempt to delete her from the first table:
INSERT INTO employees
VALUES(1000,'123-45-6789','Paola Caporale');
INSERT INTO employees_telephone
VALUES(1000,2,'+39 343-12-34-5678');
DELETE FROM employees WHERE emp_id = 1000;
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails
('human_resources'.'employees_telephone',
CONSTRAINT 'employees_telephone_ibfk_1'
FOREIGN KEY ('emp_id') REFERENCES 'employees' ('emp_id')
)
As you can see, we cannot delete the employee from the employees table and leave the stray row of data in the employees_telephone table. We have to delete the data in employees_telephone first, before deleting the related data