I am converting all my applications from MS Access to PostgreSQL and I have a few personal-use databases. These MS databases have text fields as the primary keys, so I can type the name of something in a reference table rather than typing an arbitrary ID number.
I want to create the databases in postgresql properly (with ID numbers as primary key) - but don't want to have to look up the ID all the time when manually entering info into the database.
Is there a way I can use an INSERT statement (or something similar) and use a name field, rather than the auto ID?
Example of a record table and reference table:
CREATE TABLE testreport.records ( recordid serial NOT NULL, userid integer, CONSTRAINT records_pkey PRIMARY KEY (recordid), CONSTRAINT records_r_user_fkey FOREIGN KEY (userid) REFERENCES testreport.r_user (userid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION )
CREATE TABLE testreport.r_user ( userid serial NOT NULL, username text, CONSTRAINT r_user_pkey PRIMARY KEY (userid) )
I want to be able to insert data by typing in the
username, rather than having to type in the
userid. Is there a way I can do this?
Note: All fields in
username are unique