mysql - 主键重复输入

Question

给出的任务是:

Create a new relational table to store information about the company names of all suppliers and the total number of products supplied by each supplier. Enforce, the appropriate consistencyconstraints on the new table. Next, copy into the new table information about the company names of all suppliers and the total number of products supplied by each supplier.

我收到错误键“PRIMARY”的重复条目当我尝试运行此脚本时

     CREATE TABLE COMPANY_AND_SUPPLIERS (

        COMPANY_NAME VARCHAR (40) NOT NULL DEFAULT 'EMPTY',
        PRODUCT_NAME VARCHAR(40) NOT NULL DEFAULT 'EMPTY' ,
        TOTAL_PRODUCTS VARCHAR(40) NOT NULL DEFAULT 'EMPTY',
        CONSTRAINT SUPPLIER_PKEY PRIMARY KEY(COMPANY_NAME) ,
        CONSTRAINT SUPPLIER_FKEY FOREIGN KEY (COMPANY_NAME) REFERENCES SUPPLIER(COMPANY_NAME)


        );

    INSERT INTO COMPANY_AND_SUPPLIERS(COMPANY_NAME, PRODUCT_NAME)
    SELECT  DISTINCT SUPPLIER.COMPANY_NAME, PRODUCT.PRODUCT_NAME
    FROM SUPPLIER, PRODUCT;

    UPDATE COMPANY_AND_SUPPLIERS
    SET TOTAL_PRODUCTS = (SELECT COUNT(*) AS TOTALPRODUCTS
    FROM PRODUCT);

该练习的全部目的是复制所有供应商的公司名称以及每个供应商提供的产品总数。

给出的表格

CREATE TABLE SUPPLIER
(
    COMPANY_NAME    VARCHAR(40) NOT NULL,
    CONTACT_NAME    VARCHAR(30),
    CONTACT_TITLE   VARCHAR(30),
    ADDRESS         VARCHAR(60),
    CITY        VARCHAR(15),
    REGION      VARCHAR(15),
    POSTAL_CODE     VARCHAR(10),
    COUNTRY         VARCHAR(15),
    PHONE       VARCHAR(24),
    FAX         VARCHAR(24),
    HOME_PAGE       VARCHAR(500),
    CONSTRAINT PK_SUPPLIER PRIMARY KEY (COMPANY_NAME)  
);

CREATE TABLE PRODUCT
(
    PRODUCT_NAME    VARCHAR(40)     NOT NULL,
    SUPPLIER_NAME   VARCHAR(40)     NOT NULL,
    CATEGORY_NAME   VARCHAR(30) NOT NULL,
    QUANTITY_PER_UNIT   VARCHAR(20),
    UNIT_PRICE      DECIMAL(10,2)   NOT NULL    DEFAULT 0,
    UNITS_IN_STOCK  DECIMAL(9)  NOT NULL    DEFAULT 0,
    UNITS_ON_ORDER  DECIMAL(9)  NOT NULL    DEFAULT 0, 
    REORDER_LEVEL   DECIMAL(9)  NOT NULL    DEFAULT 0,
    DISCONTINUED    CHAR(1)     NOT NULL    DEFAULT 'N',
    CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),
    CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),
    CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),
    CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
    CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),
    CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),
    CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),
    CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))
);

Answer 1

列company_name 不应是主键，因为主键 是唯一值。

以此为例

如果数据库管理员创建一个以 first_name 作为主键的表，这将是一场灾难，因为有很多人的名字都是 John

这就是为什么大多数时候，主键是一个整数，然后我们使用 this 使其唯一。方法。