# Foreign Keys + Cascade Deletes

A few things to note / remember on setting up foreign keys.

Be sure to set the type to InnoDB.

$ CREATE TABLE foo ... TYPE=InnoDB;

The primary key (if an integer) needs to be unsigned and not null.  It 
also needs to be indexed, in addition to whatever other attributes are 
being added (primary key, unique, etc.).

$ CREATE TABLE customers (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
name VARCHAR(255), INDEX (id), PRIMARY KEY (id)) TYPE=InnoDB;

The reference key also needs to be unsigned and not null, as well as the 
same data type.  It needs to be indexed too along with the foreign key 
constraint.

$ CREATE TABLE orders (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, fkey
INTEGER UNSIGNED NOT NULL, INDEX (fkey), FOREIGN KEY (fkey) REFERENCES 
customers (id) ON DELETE CASCADE, PRIMARY KEY (id)) TYPE=INNODB;

*** Adding Foreign Keys ***

This is very important, since MySQL will throw the wrong error if this 
is not setup correctly.

ALTER TABLE pro_sales ADD CONSTRAINT FOREIGN KEY pro_sales(pro) 
REFERENCES pros(id) on delete cascade;

Both pro_sales(id) and pros(id) need to have an index on them, as well 
as be same data type (integer, unsigned).  Not sure if IS NULL is 
important or not.

If you try to add one and they do not match, you will actually get a 
"Cannot create table" error instead:  ERROR 1005: Can't create table 
'./aht/#sql-c491_4cb8.frm' (errno: 150)

# Force an updated time stamp on insert

ALTER TABLE foo ADD COLUMN update_date timestamp NOT NULL;

Stupidly enough, MySQL will also update the timestamp value anytime you 
update any field in the row.

# Resetting tab-completion

REHASH;

# Clear the current line

Ctl-U

# Rename a column

ALTER TABLE table CHANGE foo bar varchar(255) not null default '';

I don't think there's a way to *just* change the name... you have to 
reassert the data type as well.

# Show indexes

SHOW INDEX FROM table;

# Show the db/table schema

show create database foo;

show create table bar;

# Fix a primary key

alter table customers drop primary key;
ALTER TABLE `customers` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

# import CSV file

LOAD DATA INFILE '/importfile.csv' INTO TABLE test_table FIELDS TERMINATED BY
',' LINES TERMINATED BY '\n' (field1, filed2, field3);

# Update cross tables (I can't believe this worked)
update counties c, states s set c.state = s.id  where c.state_name = 
s.state;