MySQL - FOREIGN KEY 外键

要通过 JavaScript 程序在字段上应用外键，我们需要使用 mysql2 库的 query() 函数在 CREATE 语句中执行 FOREIGN KEY 关键字，如下所示 -

sql = `CREATE TABLE customers(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID) )`;
con.query(sql);

要通过 Java 程序在字段上应用外键，我们需要使用 JDBC 函数 execute() 在 CREATE 语句中执行 FOREIGN KEY 关键字，如下所示 -

String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))";
statement.execute(sql);

要通过 Python 程序在字段上应用外键，我们需要使用 MySQL Connector/Python 的 execute() 函数在 CREATE 语句中执行 FOREIGN KEY 关键字，如下所示 -

foreign_key_query = 'CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customer(cust_ID))'
cursorObj.execute(foreign_key_query)

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //连接到 MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);
 //creating a column that is foreign key!
  sql = `CREATE TABLE customerss(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID) )`;
  con.query(sql);
 //describe table details
  sql = "DESCRIBE TABLE customers";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});      

输出

生成的输出如下 -

[
    {
      id: 1,
      select_type: 'SIMPLE',
      table: 'customers',
      partitions: null,
      type: 'ALL',
      possible_keys: null,
      key: null,
      key_len: null,
      ref: null,
      rows: 1,
      filtered: 100,
      Extra: null
    }
]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ForeignKey {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");
        //Create a foreign key in the customer table...!;
         String sql = "CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))";
         statement.execute(sql);
         System.out.println("Foreign key created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE customer");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+" "
                    +resultSet.getString(3)+ " "+ resultSet.getString(4));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}    

输出

获得的输出如下所示 -

Connected successfully...!
Foreign key created successfully...!
Customer_Id int YES MUL
Customer_Name varchar(30) YES

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# 创建表
foreign_key_query = '''CREATE TABLE customer(Customer_Id INT, Customer_Name VARCHAR(30), CONSTRAINT tut_cutomers FOREIGN KEY (Customer_Id) REFERENCES customers(cust_ID))'''
cursorObj.execute(foreign_key_query)
connection.commit()
print("Foreign key column is created successfully!")
cursorObj.close()
connection.close()       

输出

以下是上述代码的输出 -

Foreign key column is created successfully!