Why Your Internal System Should Use Internal IDs (Not External Auth IDs)

Many systems today integrate with external identity providers—services that handle user authentication and issue secure tokens for login. These providers typically return a unique user identifier (like an external_oid, sub, or auth_id) with each session.

It might seem convenient to use these external identifiers as the primary key across your database. After all, they are unique and already available from the identity provider.

However, using external IDs as your system’s core identity is a bad idea. Here’s why you should always generate and use an internal uid, and treat external IDs only as lookup references.

1. Use Internal uid as Primary Key

Your users table should have a system-generated internal ID like:

uid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY

This key is:

• Small and fast to index
• Efficient for foreign key relationships
• Decoupled from any third-party identity provider

Avoid using this:

PRIMARY KEY (external_oid)

Why? Because you're letting an outside system dictate how your internal database functions — and that creates unnecessary fragility.

2. Store External IDs as Lookup Fields

External identifiers (e.g. external_oid, auth_id, sub) should be stored with a UNIQUE constraint, not as primary keys.

external_oid VARCHAR(100) UNIQUE NOT NULL

These fields should be used only during authentication, not in internal business logic or table relationships.

3. Map External IDs to Internal IDs at Login

Here’s the typical flow:

1. A user logs in using an external identity system.
2. Your app receives a token containing an external_oid.
3. You attach that uid to the session or your own internal JWT.
4. All further API interactions use the internal uid.

You query:

SELECT uid FROM users WHERE external_oid = ?;

That’s it. No need to use or expose the external identifier again until the next login or token refresh.

4. Avoid Foreign Keys to External IDs

You should never create foreign keys to an external identifier like external_oid. Instead, always use your internal uid.

-- ❌ Don't do this
FOREIGN KEY (user_oid) REFERENCES users(external_oid)

-- ✅ Do this
FOREIGN KEY (user_uid) REFERENCES users(uid)

This keeps your schema optimized and under your control.

5. Benefits of Using Internal IDs

6. Supporting Multiple Identity Providers

You can expand your users table to support various identity sources by adding additional UNIQUE external ID fields:

external_oid_provider_a  VARCHAR(100) UNIQUE
external_oid_provider_b  VARCHAR(100) UNIQUE
external_oid_provider_c  VARCHAR(100) UNIQUE

Alternatively, normalize this into a user_identities table:

CREATE TABLE user_identities (
  uid BIGINT UNSIGNED,
  provider ENUM('provider_a', 'provider_b', 'provider_c'),
  external_oid VARCHAR(100),
  UNIQUE(provider, external_oid),
  FOREIGN KEY (uid) REFERENCES users(uid)
);

This model scales better and keeps the users table clean.

7. Session Validity: Use External ID Only at Boundaries

If your system periodically re-checks session validity or refreshes tokens, you may still need to extract and validate the external ID. That’s fine — but it should not replace the internal uid in your app's core operations.

Always translate the external identity into your internal uid, and operate solely on uid within your app and database.

8. Final Schema Pattern

Your users table:

CREATE TABLE users (
  uid BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  external_oid VARCHAR(100) UNIQUE NOT NULL,
  email VARCHAR(255),
  -- other fields...
);

Your related tables:

CREATE TABLE user_images (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_uid BIGINT UNSIGNED,
  FOREIGN KEY (user_uid) REFERENCES users(uid)
);

9. Other Considerations

• ✅ Index your external IDs for fast lookup at login
• ✅ Use uid in analytics, audit logs, and internal APIs
• ✅ Normalize external identities into separate tables if supporting multiple sources
• ⚠️ Avoid exposing external_oid in URLs or client-side logic

✅ Finally

Your application should treat external identity systems as external. Let them handle authentication and give you unique IDs, but don’t let those IDs dictate your database schema.

Instead, build your internal system around a simple, performant, and provider-agnostic uid. Doing so makes your application faster, more maintainable, and resilient to change — no matter which identity system you use today or switch to tomorrow.

Support Us