mysql - 如何在 customer 下创建一个引用 employer 表中的 "id"的列

Question

CREATE TABLE College (
    CollegeID INTEGER AUTO_INCREMENT NOT NULL,
    Collegename varchar(50),
    PRIMARY KEY(CollegeID));

INSERT INTO `College` (`CollegeID`, `Collegename`) VALUES
(1, 'Harvard'),
(2, 'Princeton'),
(3, 'Columbia'),
(4, 'Georgetown'),
(5, 'Yale');



 CREATE TABLE Customer (
    CustomerID INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    FirstName varchar(20),
    LastName varchar(20),
    DateHired DATE,
    CollegeID INTEGER REFERENCES College(CollegeID));



INSERT INTO `Customer` (`CustomerID`, `FirstName`, `LastName`, `DateHired`, `CollegeID`) VALUES
(111, 'Johnny', 'Silvia', '20100301', 1),
(222, 'Billy', 'Blank', '20070111', 2),
(333, 'Susan', 'Anderson', '20021222', 3),
(444, 'Samantha', 'Love', '19990521', 3),
(555, 'Bill', 'Gray', '20010913', 5);



CREATE TABLE `employer` (
  `id` INTEGER NOT NULL,
  `EmployerName` varchar(60) NOT NULL,
  PRIMARY KEY(id));


INSERT INTO `employer` (`id`, `EmployerName`) VALUES
(1111, 'McDonalds');

Answer 1

如果客户可以有一个但只有一个雇主，那么可以，将雇主 ID 引用添加到您的客户表中。

如果一个客户可以有多个雇主，那么您必须创建另一个包含客户 ID 和雇主 ID 的表。

 CREATE TABLE Customer (
    CustomerID INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    EmployerID INTEGER REFERENCES employer(id),
    FirstName varchar(20),
    LastName varchar(20),
    DateHired DATE,
    CollegeID INTEGER REFERENCES College(CollegeID));

我不太了解 mysql，我只是采用了您使用的语法，但是如果您想创建外键引用，请查看此链接 http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html