SQL Primary key struggle

Question

I am trying to create a table for suppliers group and when I try to create it it throws an error.

Here's the code :

CREATE TABLE GROUPS_PLUS_SUPPLIERS  
(
    PRODUCT_GROUP_SUPPLIER_ID NUMBER(3),
    GROUP_ID NUMBER(4),    
    GROUP_NAME VARCHAR2(255),   
    SUPPLIER_ID NUMBER(4),    

    CONSTRAINT PRODUCT_GROUP_SUPPLIER_ID_PK PRIMARY KEY (PRODUCT_GROUP_SUPPLIER_ID),   
    CONSTRAINT FK_SUPPLIER_ID FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIERS (SUPPLIER_ID)
);

CREATE TABLE PRODUCTS
(
    PRODUCT_ID NUMBER (4),
    PRODUCT_DESCRIPTION VARCHAR2 (255),
    PRODUCT_SIZE VARCHAR2 (10),
    PRODUCT_GROUP NUMBER (4),
    PRODUCT_PRICE NUMBER(4),
    NO_IN_STOCK NUMBER (4),
    REORDER_LEVEL NUMBER (4),

    CONSTRAINT PRODUCTS_ID_PK PRIMARY KEY (PRODUCT_ID),
    CONSTRAINT FK_GROUP_PLUS_SUPPLIERS_ID FOREIGN KEY (PRODUCT_GROUP) REFERENCES GROUPS_PLUS_SUPPLIERS(GROUP_ID)
);

This is the error message I am getting :

ORA-02270: no matching unique or primary key for this column-list

Here's what I am trying to add in group plus suppliers:

INSERT INTO GROUPS_PLUS_SUPPLIERS VALUES (1,705,'flavoured oil',5588);
INSERT INTO GROUPS_PLUS_SUPPLIERS VALUES (2,705,'flavoured oil',5509);
INSERT INTO GROUPS_PLUS_SUPPLIERS VALUES (3,800,'spice',5543);
INSERT INTO GROUPS_PLUS_SUPPLIERS VALUES (4,800,'spice',5579);
INSERT INTO GROUPS_PLUS_SUPPLIERS VALUES (5,800,'spice',5584);

Any help would be appreciated!

Answer 1

Since your GROUP_ID column is not unique, and foreign key constraints cannot be made to non-unique values (which single row does it relate to?), and given that your GROUPS_PLUS_SUPPLIERS table also uses SUPPLIER_ID, then you have to have a composite foreign key:

CREATE TABLE GROUPS_PLUS_SUPPLIERS (
    PRODUCT_GROUP_SUPPLIER_ID NUMBER(3), -- better not to have this column
    GROUP_ID NUMBER(4),
    GROUP_NAME VARCHAR2(255), -- violates 2nd normal form!
    SUPPLIER_ID NUMBER(4),

    CONSTRAINT PRODUCT_GROUP_SUPPLIER_ID_PK PRIMARY KEY (PRODUCT_GROUP_SUPPLIER_ID),
    CONSTRAINT FK_SUPPLIER_ID FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIERS (SUPPLIER_ID)
    -- A new constraint
    CONSTRAINT UQ_PRODUCT_GROUP_SUPPLIER_ID_GROUP_ID UNIQUE (SUPPLIER_ID, GROUP_ID)
);

CREATE TABLE PRODUCTS (
    PRODUCT_ID NUMBER (4),
    PRODUCT_DESCRIPTION VARCHAR2 (255),
    PRODUCT_SIZE VARCHAR2 (10),
    PRODUCT_GROUP NUMBER (4),
    PRODUCT_PRICE NUMBER(4),
    NO_IN_STOCK NUMBER (4),
    REORDER_LEVEL NUMBER (4),

    CONSTRAINT PRODUCTS_ID_PK PRIMARY KEY (PRODUCT_ID),
    -- a changed constraint
    CONSTRAINT FK_GROUPS_PLUS_SUPPLIERS_SUPPLIER_ID_GROUP_ID 
       FOREIGN KEY ( SUPPLIER_ID, PRODUCT_GROUP)
       REFERENCES GROUPS_PLUS_SUPPLIERS(SUPPLIER_ID, GROUP_ID)
);

It's possible I could be off base in this recommendation, and the FK instead needs to point to the GROUPS table, which you need to create if it doesn't exist. It's also a possible solution to make your FK point to the PRODUCT_GROUP_SUPPLIER_ID column, but I promise you that doing that will create serious problems for you down the road, when you discover that to query your products table by group you will always be forced to join to another table. I predict quite confidently that you will deeply regret it if you do that.

There are also some serious issues with the database design.

1. Seeing your updated example for what you want in the GROUPS_PLUS_SUPPLIERS table, it is very bad for GROUP_NAME to be in that table, because this violates second normal form. You need a GROUPS table with the GROUP_ID and the GROUP_NAME columns there.

2. The GROUPS_PLUS_SUPPLIERS table appears to be a many-to-many join table, and almost certainly doesn't need its own ID column. I promise that this is true 99% of the time, and that it's better for any other tables referring to this logical relation (a unique relationship between a SUPPLIER_ID and a GROUP_ID) to just use a composite key. You'll thank me down the road for this.

3. NUMBER(4) seems awfully low. Are you sure there will never, ever, ever be more than 9999 products? Even for suppliers this sounds too low. Save yourself a major headache later and make these reasonably large enough to accommodate a real-world enterprise-level scenario.

Also, please forgive me if I'm being uncomplimentary, but your naming scheme needs some work. I know some older versions of Oracle require all upper case and can't handle lower case, so I guess ignore that part if that's what you're working with.

1. Don't use all upper case with underscores. At least use Pascal Case, or all lower case with underscores. Better would be GroupID or GroupId and so on.
2. Name columns the same in all tables. Don't call it GROUP_ID in one table and PRODUCT_GROUP in another. Not doing so is, frankly, ridiculous and will lead to confusion and hatred for you by future developers. The next developer to work with this database after you had better not know your address!
3. GROUPS_PLUS_SUPPLIERS is unnecessarily wordy. Just use SUPPLIERS_GROUPS.
4. Don't put the table name before every conceivable column. Just don't. I suppose it's okay for column names that are common to many tables, such as Description, but don't do it for the others. (In fact, that's why in my own tables I stopped using Descr or Description entirely, and now just call the column the singular of the table name--although my tables are singular, too, e.g., the ProductStatus table would have name column ProductStatus instead of ProductStatusDescription or Description or some other monstrosity.)

Here's what I think a far more sensible naming scheme and database designe would look like:

CREATE TABLE Groups (
   GroupID NUMBER(4),
   GroupName VARCHAR2(255), -- I would normally call this Group but that's reserved

   CONSTRAINT PK_Groups PRIMARY KEY (GroupID),
   CONSTRAINT UQ_Groups_GroupName UNIQUE (GroupName)
);

CREATE TABLE SupplierGroups (
    GroupID NUMBER(4),    
    SupplierID NUMBER(4),

    CONSTRAINT PK_SupplierGroups PRIMARY KEY (SUPPLIER_ID, GROUP_ID),   
    CONSTRAINT FK_SupplierID FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID),
    CONSTRAINT FK_GroupID FOREIGN KEY (GroupID) REFERENCES Groups (GroupID)
);

CREATE TABLE Products (
    ProductID NUMBER (4),
    ProductDescription VARCHAR2 (255),
    Size VARCHAR2 (10),
    GroupID NUMBER (4),
    Price NUMBER(4),
    NoInStock NUMBER (4),
    ReorderLevel NUMBER (4),

    CONSTRAINT PK_Products PRIMARY KEY (ProductID),
    CONSTRAINT FK_Products_SupplierID_GroupID FOREIGN KEY (SupplierID, GroupID)
       REFERENCES SupplierGroups (SupplierID, GroupID)
);

Answer 2

The constraint FK_GROUP_PLUS_SUPPLIERS_ID on the PRODUCTS table should not reference GROUPS_PLUS_SUPPLIERS(GROUP_ID). It should reference GROUPS_PLUS_SUPPLIERS(PRODUCT_GROUP_SUPPLIER_ID) instead.

...
CONSTRAINT FK_GROUP_PLUS_SUPPLIERS_ID FOREIGN KEY (PRODUCT_GROUP) REFERENCES GROUPS_PLUS_SUPPLIERS(PRODUCT_GROUP_SUPPLIER_ID)
...

If you really intend that to be a FK to just the GROUP_ID, then there should be a GROUP table, where GROUP_ID is the primary key, that you should reference.

...
CONSTRAINT FK_GROUP_PLUS_SUPPLIERS_ID FOREIGN KEY (PRODUCT_GROUP) REFERENCES GROUP(GROUP_ID)
...

Answer 3

If lack of uniqueness is the real reason for the error, then you must use a compound PK to ensure uniqueness:

CREATE TABLE GROUPS_PLUS_SUPPLIERS  
(
    PRODUCT_GROUP_SUPPLIER_ID NUMBER(3),
    GROUP_ID NUMBER(4),    
    GROUP_NAME VARCHAR2(255),   
    SUPPLIER_ID NUMBER(4),    

    CONSTRAINT GROUPS_PLUS_SUPPLIERS_PK PRIMARY KEY (PRODUCT_GROUP_SUPPLIER_ID,GROUP_ID,GROUP_NAME,SUPPLIER_ID),   
    CONSTRAINT FK_SUPPLIER_ID FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIERS (SUPPLIER_ID)
);

This will allow you to add duplicate GROUP_ID values and yet satisfy the required matching uniqueness.

Answer 4

The error message says what you are missing. The foreign key to GROUPS_PLUS_SUPPLIERS(GROUP_ID) means you need to add a unique index on this column.

CREATE TABLE GROUPS_PLUS_SUPPLIERS  
(
    PRODUCT_GROUP_SUPPLIER_ID NUMBER(3),
    GROUP_ID NUMBER(4),    
    GROUP_NAME VARCHAR2(255),   
    SUPPLIER_ID NUMBER(4),    

    CONSTRAINT PRODUCT_GROUP_SUPPLIER_ID_PK PRIMARY KEY (PRODUCT_GROUP_SUPPLIER_ID),   
    CONSTRAINT FK_SUPPLIER_ID FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIERS (SUPPLIER_ID),
    CONSTRAINT AK_GROUP_ID UNIQUE(GROUP_ID)
);