Foreign key on two columns

For my school project i needed to create a database. This is my ERD.

ERD

In SQL i create the Category table:

CREATE table "Category" (
  "Id" NUMBER(5) NOT NULL,
  "SubCategoryId" NUMBER(5) NULL,
  "Name" VARCHAR2(32) NOT NULL,
  CONSTRAINT "Category_pk" PRIMARY KEY ("Id")
);

ALTER TABLE "Category" ADD CONSTRAINT "Category_fk" FOREIGN KEY ("SubCategoryId") REFERENCES "Category" ("Id");

Product table

CREATE table "Product" (
  "Id" NUMBER(5) NOT NULL,
  "CategoryId" NUMBER(5) NOT NULL, /* Category.Id or Category.SubCategoryId */
  "Name" VARCHAR2(32) NOT NULL,
  "Brand" VARCHAR2(32),
  "Type" VARCHAR2(32),
  "Year" NUMBER(4),
  CONSTRAINT "Product_pk" PRIMARY KEY ("Id")
);

The product table has a CategoryId (foreign key) on Category.Id, but the Product.CategoryId also needs a foreign key on Category.SubCategoryId.

Is this possible, or should i create an extra table called “SubCategory” and then add foreign keys to Category.Id and SubCategory.Id?


Source: oracle

Leave a Reply