MySQL 5.5 my note

Create table with AUTO_INCREMENT as primary key

CREATE TABLE animals (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

Create table with Foreign Key

CREATE TABLE animals (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    category int(11) NOT NULL,
    PRIMARY KEY (id),
    KEY fk_category (category),
    CONSTRAINT fk_category FOREIGN KEY (category) REFERENCES category(id)   
);

Alter table adding Foreign Key

ALTER TABLE animals CONSTRAINT fk_category FOREIGN KEY (category) REFERENCES category(id);

Alter column NOT NULL

ALTER TABLE animals MODIFY category int NOT NULL;

Alter column name from category to category

ALTER TABLE animals CHANGE category category int NOT NULL;


Reference:

• http://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html
• http://dev.mysql.com/doc/refman/5.7/en/alter-table.html