Duplicate Entry for Primary Key

Question

The task given is:

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.

I'm recieving an error Duplicate Entry for key "PRIMARY" When i try and run this script


     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);

The Whole purpose of the exercise is to copy company names of all suppliers and the total number of products supplied by each supplier.

TABLES GIVEN

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'))
);

Oh, it does. It's producing **distinct rows**, not distinct `COMPANY_NAME`. Revisit your solution. — The Impaler, May 17 '19 at 00:11
@TheImpaler does it make a difference that theres no duplicates? — Andrew Van, May 17 '19 at 00:16
So every supplier can supply every product? That's what the `INSERT` statement does with `SUPPLIER, PRODUCT`. Its also odd naming where TOTAL_PRODUCTS is the total of all the products. Why is TOTAL_PRODUCTS as `VARCHAR` instead of a numeric type. Suggest using "NULL", instead of the defaults string 'EMPTY' if you need not filled table values. — danblack, May 17 '19 at 00:24
@danblack Yes Many Suppliers Can Supplier Many products. In regards to the empty defaults, if i dont do this it says they need a default value. — Andrew Van, May 17 '19 at 00:27
Your `COMPANY_AND_SUPPLIERS` table has `COMPANY_NAME` as a primary key, i.e. each company can only appear once but your `INSERT` implies there should be a row for each product + company meaning more than one row per company. What is the intent of this table? — smashed-potatoes, May 17 '19 at 00:29
@AndrewVan That's because you declare them `NOT NULL`, so you can't use `NULL` as the default value. — Barmar, May 17 '19 at 00:29
@danblack but my main issue is how do only make company names distinct then. It shouldnt be returning duplicate values as theres only one of each name. — Andrew Van, May 17 '19 at 00:30
The primary key should probably be composite `(COMPANY_NAME, PRODUCT_NAME)`, to allow a company to sell multiple products. — Barmar, May 17 '19 at 00:30
@AndrewVan You're duplicating the company name for each product. You have `Company1 Product1, Company1 Product2, Company1 Product3, Company2 Product1, Company2 Product2, Company2 Product3, ...` — Barmar, May 17 '19 at 00:31
If that's not what you want, which single product should it list for each company? — Barmar, May 17 '19 at 00:32
@smashed-potatoes "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.* — Andrew Van, May 17 '19 at 00:32
You're supposed to have two tables. One is the relational table between companies and products, it can have multiple rows for each company. The second is a table just for companies, it has one row for each company, and just the total number of products, not the product names. — Barmar, May 17 '19 at 00:34
@Barmar yes the relational table between companies and products is the one i created. — Andrew Van, May 17 '19 at 00:36
That table shouldn't have `COMPANY_NAME` as the primary key. Only the `COMPANY` table should be like that. And this table shouldn't have `TOTAL_PRODUCTS`, that should be in the `COMPANY` table. — Barmar, May 17 '19 at 00:39
@Barmar they give us a table for the supplier and the product if that helps — Andrew Van, May 17 '19 at 00:39
@Barmar so how would i store the total number of products then? I thought you'd have to make a new Column to do this — Andrew Van, May 17 '19 at 00:44

Barmar · Answer 1 · 2019-05-17T01:01:18.040

0

Your table isn't supposed to contain the product names, just the company and total products. The PRODUCTS table already has the supplier names in it for each product. So you just need to count the number of products for each supplier from that table.

CREATE TABLE Company_Totals (
    Company_name VARCHAR(40) NOT NULL,
    Total_Products INT(11) NOT NULL,
    PRIMARY KEY (Company_name),
    FOREIGN KEY (Company_name) REFERENCES Supplier(Company_name)
);

INSERT INTO Company_Totals (Company_name, Total_Products)
SELECT SUPPLIER_NAME, COUNT(*)
FROM PRODUCT
GROUP BY SUPPLIER_NAME;

edited May 17 '19 at 01:01

answered May 17 '19 at 00:47

Barmar

741,623
53
500
612

yea they give us a Product Table and a Supplier Table. The Product table doesn't have a Company Name but the supplier table does. @Barmar – Andrew Van May 17 '19 at 00:51
i tried doing your query but the total count comes up as 1 for all the products. @Barmar – Andrew Van May 17 '19 at 00:53
They need to give you a third table that links products and suppliers. – Barmar May 17 '19 at 00:54
Please see my edit i added the two tables given @Barmar – Andrew Van May 17 '19 at 00:59
The `PRODUCT` table you're given is the table I'm talking about. It has both the company name and product name. – Barmar May 17 '19 at 01:03
EDIT I did it thankyou so much for your help @Barmar. Apologies if i sound clueless, im still fairly new to sql and im struggling to interpurate the questions that im given – Andrew Van May 17 '19 at 01:06
just another quick question. Is this similar to what i just did? "Add to a relational table EMPLOYEE information about the total number of orders handled by each employee" @Barmar – Andrew Van May 17 '19 at 01:19
That sounds similar. – Barmar May 17 '19 at 01:20
Would i just be adding a Column to Employee for the total Number? @Barmar – Andrew Van May 17 '19 at 01:25
That sounds like what they're asking you to do. Then you need to do an `UPDATE` with `JOIN` to get the count for each employee. – Barmar May 17 '19 at 01:27
If you have trouble getting that to work, you need to ask a new question, we're not going to solve this in comments. – Barmar May 17 '19 at 01:27
Okay ill do so and then link it here. Ive attempted the question with a working sccript but im unsure if its correct @Barmar – Andrew Van May 17 '19 at 07:30
hello @Barmar. I actually figured the question out! but theres one i am extremely stuck on https://stackoverflow.com/questions/56184370/how-to-delete-from-a-table-without-having-to-drop-a-constraint – Andrew Van May 17 '19 at 11:56

score 0 · Answer 2 · answered May 17 '19 at 00:49

The column company_name should not be a primary key because a primary key is a unique value.

Take this as an example

If a Database administrator creates a table with first_name as the primary key, it would be a disaster since there are a lot of people who has John as their first name

That is why most of the time, the primary key is a integer, then we make it unique using this method.

Duplicate Entry for Primary Key

2 Answers2