Re: Using complex PRIMARY KEY - Mailing list pgsql-general

This is our first project using PostgerSQL, where I have a problem I cant solve on a neat way (I assume PGSQL should provide a nice solution...).

So we have an old xBase based program we are trying to port to PostgreSQL while we should keep the original data structure especially the ID fields must be kept as this IDs are already used in other systems.

The problem is with two table, one is storing the data of houses the other the data of tenants in a given houses.

 

 

Something like this:

CREATE TABLE house (

house_id SERIAL,

.....

CONSTRAINT pk_house_id PRIMARY KEY(house_id)

 

) WITHOUT OIDS;

 

 

CREATE TABLE tenant (

tenant_id SERIAL,

house_id INTEGER REFERENCES house(house_id),

.....

CONSTRAINT pk_tenant_house_id PRIMARY KEY(tenant_id, house_id)

 

) WITHOUT OIDS;

 

For a given house I would like to start the numbering of tenants from 1. Each house could have tenant_ID=1, obviously in this case the house_ID will differ. The combination of tenant_ID and house_ID will be the unique identifier of each tenant.

 

I'm just looking for the best solution to insert new rows into the tenant database without worrying about keeping the above mentioned logic in mind. Should I create a stored procedure to add a new tenant and this will calculate the new house_id+tenant_id combination (only the house_id would be passed to the stored procedure, the tenat_id will be calculated by the sp). In this case how can I avoid that two concurrent user would try to add records in the same time without getting an exception due to violating the pk_tenant_house_id constraint? Or should I add a  new field in the house table storing the last issued tenant_id in the given house (max_tenant_id) which will be used while adding a new record to tenant, and will be updated by a trigger on the tenant table? Or am I thinking on a wrong way and there is a better mechanism provided by PostgreSQL for this problem?

 

Any other suggestions would be greatly appreciated.