Mysql foreign Key Usage Analysis

Mysql foreign key usage is the basic knowledge in the Mysql database. The following describes Mysql foreign Key Usage in detail. If you are interested in this, take a look.

Mysql foreign key constraints were created with Mysql last night and have never been successful. We found this one today. It turns out that only InnoDB tables can use Mysql Foreign keys.

Only InnoDB tables can use foreign keys.

 
 

  
  
CREATE TABLE person (   
  
  
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,   
  
  
name CHAR(60) NOT NULL,   
  
  
PRIMARY KEY (id)   
  
  
)type=innoDB;   
  
  
 
  
  
CREATE TABLE shirt (   
  
  
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,   
  
  
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,   
  
  
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,   
  
  
owner SMALLINT UNSIGNED NOT NULL,   
  
  
FOREIGN KEY (owner) REFERENCES PERSON(id)   
  
  
ON DELETE CASCADE   
  
  
ON UPDATE CASCADE,   
  
  
PRIMARY KEY (id)   
  
  
)type=innoDB;   
  
  
 
 
 

Then open MySQL Administrator and finally see something about the foreign key. Try to manually delete it and use MySQL Administrator to create it.

 
 

  
  
INSERT INTO person VALUES (NULL, 'Antonio Paz');   
  
  
 
  
  
SELECT @last := LAST_INSERT_ID();   
  
  
 
  
  
INSERT INTO shirt VALUES   
  
  
(NULL, 'polo', 'blue', @last),   
  
  
(NULL, 'dress', 'white', @last),   
  
  
(NULL, 't-shirt', 'blue', @last);   
  
  
 
  
  
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');   
  
  
 
  
  
SELECT @last := LAST_INSERT_ID();   
  
  
 
  
  
INSERT INTO shirt VALUES   
  
  
(NULL, 'dress', 'orange', @last),   
  
  
(NULL, 'polo', 'red', @last),   
  
  
(NULL, 'dress', 'blue', @last),   
  
  
(NULL, 't-shirt', 'white', @last);   
  
  
 
  
  
SELECT * FROM person;   
  
  
 
  
  
SELECT * FROM shirt;   
  
  
 
 
 

Test UPDATE Association

 
 

  
  
UPDATE PERSON SET id=3 WHERE id=1;   
  
  
SELECT * FROM shirt;   
 
 

Key to test DELETE

 
 

  
  
DELETE FROM PERSON WHERE id=3;   
  
  
SELECT * FROM shirt;  
 
 

New Pricing strategies for MySQL database products

Take you to the DB2 Character Set and MySql Character Set

View MySQL database table commands

Hash-based Mysql table sharding

How MySQL defines foreign keys