Creating table with Identity related too other column in table

Question

I'm trying to create a table that has a primary key (Department_ID) that auto assigns(not hard). However the assignment would be based on the exsisting value in another column(Department_Name). So for example if its the department_name payroll the department_ID would be 1 for example. If I tried to insert into and had to add another payroll employee it would not auto increment. It would assign value 1 to it. I assume its constraint I am looking for, but have no idea if I'm looking at this wrong or how to write it. Here's what I have so far:

CREATE TABLE tblDepartment
(
Department_ID int NOT NULL IDENTITY,
Department_Name varchar(255) NOT NULL,
Division_Name varchar(255) NOT NULL,
City varchar(255) default 'sometown' NOT NULL,
Building int default 1 NOT NULL,
Phone varchar(255) NOT NULL,
PRIMARY KEY (Department_ID)NOT NULL,
Check (Building >=1 AND Building <= 10 )
)

CREATE TABLE tblEmployee
(
Employee_ID int NOT NULL IDENTITY,
Department_ID int ,
Employee_Name varchar(255),
Social_Security_Number varchar(255),
Work_Phone varchar(255),
Position varchar(255),
Hire_Date datetime,
Birth_Date datetime,
FOREIGN KEY (Department_ID) REFERENCES tblDepartment(Department_ID)
)

Thanks for the help!

Answer 1

You shouldn't need a Department record per employee. You'll only have one Department record per department. You'll insert the Department.Department_ID value from the corresponding Department record into the Employee.Department_ID field when you insert an Employee record.

An autoincrement field for Department.Department_ID should be fine.

According to your schema, you already have a constraint that enforces this.

Answer 2

A TRIGGER on insert into tblEmployee that reads tblDepartment and sets the key accordingly might be a solution.

What happens if an employee changes department?