CODEWITHSUNDEEP
sqlitedatabase-design
Deftness
Deftness

Reputation: 365

Autoincrement sub-item in relational table

I have a two tables (perfectly willing to change these tables as I'm in the planning phase):

create table building (
    buildingId integer primary key autoincrement,
    buildingName text
);

create table rooms (
    roomId integer primary key autoincrement,
    buildingId integer,
    FOREIGN KEY(buildingId) REFERENCES building(buildingId)
);

Some current sample data:

building table:   
buildingId - buildingName
1          - "house1"
2          - "house2"

rooms table:   
roomId     - buildingId
1          - 1
2          - 1
3          - 1
4          - 2
5          - 2

For the rooms table, which has a "child" relationship of a building, is there a way for each building, I restart the auto increment # for the rooms? For example, what I would want is the following:

rooms table:   
roomId     - buildingId
1          - 1
2          - 1
3          - 1
1 (changed)- 2
2 (changed)- 2

While the autoincrement adds a unique id for each room across all buildings, it seems very strange that a building could have rooms 4 & 5, but no 1 through 3 as seen in the first sample data.

Upvotes: 1

Views: 66

Answers (1)

CL.
CL.

Reputation: 180270

It would be possible to make the roomId column non-unique, so that the primary key now consists of both columns:

create table rooms (
    roomId integer,
    buildingId integer,
    PRIMARY KEY(roomId, buildingId),
    FOREIGN KEY(buildingId) REFERENCES building(buildingId)
);

However, SQLite cannot automatically assign numbers unless you have a single PK column.

Please note that this appears to be the more correct behaviour in your case: you want the room numbers to be the same number that is written on the door signs, not some random value assigned by the database, so you need to explicitly insert the correct room number anyway.

Upvotes: 3

Related Questions