Add the foreign key by using the following : Foreign Key « Key « SQL / MySQL






  1. SQL / MySQL
  2. Key
  3. Foreign Key

Add the foreign key by using the following

      
mysql>
mysql> CREATE TABLE Manufacturers
    -> (
    ->     ManfID CHAR(8)NOT NULL PRIMARY KEY,
    ->     ManfName VARCHAR(30) NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> INSERT INTO Manufacturers VALUES
    -> ('abc123', 'ABC Manufacturing'),
    -> ('def456', 'DEF Inc.'),
    -> ('ghi789', 'GHI Corporation'),
    -> ('jkl123', 'JKL Limited'),
    -> ('mno456', 'MNO Company');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE Parts
    -> (
    ->     PartID SMALLINT NOT NULL PRIMARY KEY,
    ->     PartName VARCHAR(30) NOT NULL,
    ->     ManfID CHAR(8) NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO Parts VALUES
    -> (101, 'DVD burner', 'abc123'),
    -> (102, 'CD drive', 'jkl123'),
    -> (103, '80-GB hard disk', 'mno456'),
    -> (104, 'Mini-tower', 'ghi789'),
    -> (105, 'Power supply', 'def456'),
    -> (106, 'LCD monitor', 'mno456'),
    -> (107, 'Zip drive', 'ghi789'),
    -> (108, 'Floppy drive', 'jkl123'),
    -> (109, 'Network adapter', 'def456'),
    -> (110, 'Network hub', 'jkl123'),
    -> (111, 'Router', 'mno456'),
    -> (112, 'Sound card', 'ghi789'),
    -> (113, 'Standard keyboard', 'mno456'),
    -> (114, 'PS/2 mouse', 'jkl123'),
    -> (115, '56-K modem', 'ghi789'),
    -> (116, 'Display adapter', 'mno456'),
    -> (117, 'IDE controller', 'def456');
Query OK, 17 rows affected (0.00 sec)
Records: 17  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> ALTER TABLE Parts
    -> ADD FOREIGN KEY (ManfID) REFERENCES Manufacturers (ManfID);
Query OK, 17 rows affected (0.01 sec)
Records: 17  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql>
mysql> drop table Parts;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table Manufacturers;
Query OK, 0 rows affected (0.00 sec)








Related examples in the same category

1.Define foreign key
2.RESTRICT update and delete
3.Add Foreign Key Rules
4.Use a FOREIGN KEY constraint to define the foreign key
5.Reference foreign key
6.Two foreign keys
7.On delete set null
8.Cascade delete