CODEWITHSUNDEEP
mysqlforeign-keys
delete
delete

Reputation:

How can I create a small relational database in MySQL?

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.

Upvotes: 0

Views: 477

Answers (2)

Hammerite
Hammerite

Reputation: 22340

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.

Upvotes: 1

Ólafur Waage
Ólafur Waage

Reputation: 69981

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

Upvotes: 0

Related Questions