0

If the employee is in department "red", I want to make a sub category. Since I am using tables instead of enum for this, I have to insert the static department field values manually. How would I make department red a foreign key to reference table red_object? Or is there better design to have this sub-category?

http://sqlfiddle.com/#!2/83a2a

create table department(
  deptID int auto_increment primary key,
  deptName varchar(30)
  ) ENGINE = INNODB;

create table red_object(
  objectID int auto_increment primary key,
  objectName varchar(30)
  ) ENGINE = INNODB;

create table employees(
  userId int auto_increment not null primary key,
  firstName varchar(20) not null,
  lastName varchar(20) not null,
  phone tinyint unsigned,
  fax tinyint unsigned,
  position varchar(30),
  jpeg char(50),
  deptID int,
  unique(firstName, LastName, jpeg),
  foreign key (deptID) references department(deptID)
  ) ENGINE = INNODB;

insert into department (deptName) 
  values ('red'), ('white'), ('blue'), ('black');

insert into red_object (objectName) 
  values ('red_square'), ('red_circle'), ('red_octagon');

insert into employees (firstName, lastName) values ('jane' , 'doe');

insert into employees (firstName, lastname, deptID) values ('john', 'Doe', '3');
dman
  • 10,406
  • 18
  • 102
  • 201

1 Answers1

0

Found out I can not add a foreign key to a "value" which was what I was trying to do.

dman
  • 10,406
  • 18
  • 102
  • 201