I have two tables.  

Dept
-----
deptid bigint PK
Name

Item
----
deptid bigint PK (FK)
itemid bigint PK
description

Here is an example of 2 valid records:
Dept: 1
Item: 1
Name: hammer

Dept: 2
item: 1
Name: Chainsaw


Is there a way to create sub sequence from the department sequence so these keys can be auto generated?




I have a dept of 1 (tools).
Now I want to add an item to that department
insert into item(dept,desc) values (1,'hammer');

Now add another tool:
insert into item(dept,desc) values(1,'pliers');

Select * from item;
dept    Item    Desc
----    ----    ----
   1       1    hammer
   1       2    pliers


Now we have another dept 2 (materials)
Now I want to add an item to that department
insert into item(dept,desc) values (2,'10 ft pipe');
insert into item(dept,desc) values (2,'80 lb quickcrete');
insert into item(dept,desc) values (2,'4x8 3/4in plywood');


The new select should yeild the following:
Select * from item;
dept    Item    Desc
----    ----    ----
   1       1    hammer
   1       2    pliers
   2       1    10 ft pipe
   2       2    80 lb quickcrete
   2       3    4x8 3/4in plywood

I need the item sequence to be generated automatically within each dept.