sql - auto increment on composite primary key

Question

I have a table called 'Workspaces' where the columns 'AreaID' and 'SurfaceID' work as a composite primary key. The AreaID references to another table called 'Areas' which only has AreaID as the primary key. What I want to do now is to make the surfaceID recound from 1 on every new AreaID. Right now I'm using the following code for the tables 'Areas' and 'Workspaces':

--Table 'Areas'
CREATE TABLE Areas (
AreaID INT IDENTITY(1,1) PRIMARY KEY,
Areaname VARCHAR(60) UNIQUE NOT NULL
)

--Table 'Workspaces'
CREATE TABLE Workspaces (
AreaID INT
CONSTRAINT ck_a_areaid REFERENCES Areas(AreaID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
SurfaceID INT IDENTITY(1,1)
CONSTRAINT ck_surfaceid CHECK (surfaceid > 0 AND surfaceid < 1001),
Description VARCHAR(300) NOT NULL,
CONSTRAINT ck_workspaces PRIMARY KEY (AreaID, SurfaceID)
)

When I use the code above I get a result like this when creating new workspaces in different areas:

AreaID    SurfaceID
1         1
1         2
1         3
2         4
2         5
3         6
Etc...

But I want the SurfaceID to recount from 1 on every new areaID, so my desired result would be like this:

AreaID    SurfaceID
1         1
1         2
1         3
2         1
2         2
3         1
Etc...

Does anyone know how this can be fixed?

Answer 1

You cannot easily do what you want. You can do it using triggers, but that is a rather ugly solution. You can get close to what you want by using a single identity primary key and then calculating the number you want on output:

CREATE TABLE Workspaces (
    WorkspacesId int not null identity(1, 1) primary key,
    AreaID INT,
    Description VARCHAR(300) NOT NULL,
    CONSTRAINT ck_a_areaid REFERENCES Areas(AreaID) ON DELETE CASCADE ON UPDATE NO ACTION,
);

Then when you query (or in a view):

select w.*, row_number() over (partition by areaId
                               order by WorkspaceId) as SurfaceId
from Workspaces

Note: This does not check the maximum value of surfaceId. If you really need to implement this logic, then you will need to use triggers.