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.