sql - Foreign key on two columns -

for school project needed create database. erd.

in sql create 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 categoryid (foreign key) on category.id, product.categoryid needs foreign key on category.subcategoryid.

is possible, or should create table called "subcategory" , add foreign keys category.id , subcategory.id?

the foreign key reference belongs in category table not other table. clear e-r diagram. have circled appropriate link.

i write definition more this:

create table category (   categoryid number(5) not null,   subcategoryid number(5) null,   name varchar2(32) not null,   constraint category_pk primary key (categoryid),   constraint fk_category_subcategory foreign key (subcategoryid) references category(categoryid) ); 

notes:

• don't use double quotes table , column names. make code harder write , read. corollary, don't use reserved words, spaces, or other bad characters in such names.
• i name primary key of table include table name. way, foreign key columns have same name primary key columns.
• i commend naming constraints, instead of using primary key , references on column definition line.