CODEWITHSUNDEEP
postgresqldatabase-design
John Winston
John Winston

Reputation: 1471

Do I need to use a auto-generated column as primary key, when I have a UUID from authorization server?

I am writing a resource server based on OAuth2 structure. I have a table user, and I will be storing the sub claim of JWT response into that table as a unique key for other table to reference it. I tried to do this in order to avoid checking the database for each incoming request.

If that key(sso_id) is already unique, do I still need to have a id key that will be generated automatically? Is there any performance gain/loss for omitting that key?

DROP TABLE IF EXISTS "user";
CREATE TABLE "user" (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    sso_id VARCHAR(100) NOT NULL UNIQUE
);

Upvotes: 1

Views: 1099

Answers (1)

Belayer
Belayer

Reputation: 14886

No, you do not need to generate a different Primary Key. A primary must meet 2 requirements, nothing else. It must not be null or partially null, and it must be unique within the table. As long as it meets those requirements, it is valid. Some would argue that you should generate a PK (has some validity), but that is a philosophical argument a specified requirement. You could actually use the value passed as the PK, and perhaps even a FK. I am torn between assuming that you single sign-on (sso_id) and the passed jwt_id are the same or not. Why would you define it as 100 characters, which is much larger than required if they were, but it also seems logical that they would be. I will assume they are not. Also I assume that reference to originating table is available. With that then the following would be perfectly valid.

create table "user" (
       jwt_id uuid  
     , sso_id varchar(100) not null unique
     , constraint user_pk 
                  primary_key(jwt_id)
     , constraint user_2_jwt_fk
                  foreign key (jwt_id) 
                  references jwt(jwt_id) 
);

Upvotes: 2

Related Questions