How can I create a small relational database in MySQL?

Question

I need to make a small database in MySQL that has two tables.

Clothes and ClotheType

Clothes has: ID, Name, Color, Brand, Price, ClotheTypeID ClotheType has: ID, Description

In Microsoft SQL it would be:

create table Clothes(
id int,
primary key(id),
name varchar(200),
color varchar(200),
brand varchar(200),
price varchar(200),
clothetypeid int,
foreign key clothetypeid references ClotheType(id)
)

I need this in MySQL.

Answer 1

You can use the InnoDB database engine in MySQL (instead of the default MyISAM), it has foreign key constraints.

Here is the MySQL documentation entry on the InnoDB Foreign Key Constraints

Answer 2

create table Clothes(
    id int,
    primary key(id),
    name varchar(200),
    color varchar(200),
    brand varchar(200),
    price varchar(200),
    clothetypeid int,
    constraint `constr_Clothes_clothetypeid_fk` foreign key `clothetypeid_fk` (`clothetypeid`) references `ClotheType`(`id`)
) ENGINE=INNODB

NB.

• id must be an indexed column in table ClotheType, and it must have the same type (i.e. int)
• ClotheType must also be ENGINE=INNODB. If you have already created it then you can change it using ALTER TABLE ClotheType ENGINE=INNODB

To answer the question you posed in your comment on Ólafur's answer: MySQL handles foreign key constraints natively in as much as it parses the relevant data definition language statements, but some of its storage engines do not support them. The default storage engine in most MySQL installations is MyISAM, which does not support foreign keys. InnoDB does support them.