mysql - SQL错误代码: 1364 Field 'CustomerNumber' doesn't have a default value

Question

CREATE TABLE CUSTOMER(
CustomerNumber int NOT NULL PRIMARY KEY,
CustomerFirstName char(30) NOT NULL,
CustomerLastName char(30) NOT NULL,
Phone char(12) NOT NULL
);

CREATE TABLE ENROLLMENT( 
CourseNumber int NOT NULL, 
CustomerNumber int NOT NULL, 
AmountPaid float(2) NOT NULL, 
CONSTRAINT pk_ENROLLMENT PRIMARY KEY (CustomerNumber, CourseNumber),
CONSTRAINT ENROLLMENTS_fk FOREIGN KEY (CustomerNumber) 
    REFERENCES CUSTOMER(CustomerNumber), 
CONSTRAINT fk_ENROLLMENT FOREIGN KEY (CourseNumber) 
    REFERENCES COURSE(CourseNumber) 
    ON DELETE CASCADE ON UPDATE CASCADE 
); 

INSERT INTO CUSTOMER(CustomerNumber, CustomerFirstName, CustomerLastName, Phone)
VALUES (992, 'Steve', 'Johnson', '631-998-2244');
INSERT INTO CUSTOMER(CustomerNumber, CustomerFirstName, CustomerLastName, Phone)
VALUES (998, 'Jane', 'Lovegood', '516-777-0033');
INSERT INTO CUSTOMER(CustomerNumber, CustomerFirstName, CustomerLastName, Phone)
VALUES (987, 'John', 'Lovegood', '516-777-2888');

INSERT INTO ENROLLMENT(CourseNumber)
SELECT CourseNumber
FROM COURSE
WHERE Course = 'Statistics' LIMIT 1;
INSERT INTO ENROLLMENT(CustomerNumber, AmountPaid)
SELECT CustomerNumber, 600.00
FROM CUSTOMER
WHERE CustomerLastName = 'Johnson' LIMIT 1;

Answer 1

您可以连接包含数据的表并向 INSERT INTO 语句提供完整的一行。由于列的限制，您无法执行两次单独的插入。

INSERT INTO ENROLLMENT(CourseNumber, CustomerNumber, AmountPaid)
  SELECT COURSE.CourseNumber, CUSTOMER.CustomerNumber, 600.00
  FROM COURSE
  LEFT JOIN CUSTOMER on CUSTOMER.CustomerLastName='Johnson'
  WHERE COURSE.Course = 'Statistics' LIMIT 1;

对我来说，这让人尖叫“哦，但是如果两个客户有相同的姓氏怎么办”，这将是一个问题。