How to use primary key as external reference to various tables in MySQL?

The following is the syntax-

alter table yourSecondTableName
add constraint `yourConstraintName`
foreign key(`yourSecondTableNamePrimaryKey`)
references yourFirstTableName(yourFirstTablePrimaryKeyColumnName);

To understand the above syntax, let us first create a table-

mysql> create table demo65
−> (
−> id int not null primary key,
−> name varchar(20)
−> );
Query OK, 0 rows affected (0.57 sec)

The following is the second Query for a table -

mysql> create table demo66
−> (
−> user_id int not null primary key,
−> address varchar(200)
−> );
Query OK, 0 rows affected (1.80 sec)

The following is the query for referencing the primary key as a foreign key-

mysql> alter table demo66
−> add constraint `id_fk`
−> foreign key(`user_id`)
−> references demo65(id);
Query OK, 0 rows affected (3.76 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check the overall description of the table using the SHOW CREATE TABLE command. Following is the query -

mysql> show create table demo66;

which will produce the following output -

+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| demo66 | CREATE TABLE `demo66` (
`user_id` int NOT NULL,
`address` varchar(200) DEFAULT NULL,
PRIMARY KEY (`user_id`),
CONSTRAINT `id_fk` FOREIGN KEY (`user_id`) REFERENCES `demo65` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The above is the detailed content of How to use primary key as external reference to various tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!