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?
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.