Composite primary key vs a unique constraint in a multi tenant database system

Question

I'm working on a multi tenant system, so there is a organization table that has relations to most of the other tables. So for most of my queries I query for the tableId (the primary key) of that table and the signed in organizationId. Or just for the organizationId - to get all that customers record(s).

To improve performance I guess I could use a unique constraint on the tableId + organizationId combination? But would be an even better option in this case to just set up the primary key as a composite key of the tableId + organizationId combination?

Does this answer your question? [Proper database model for a user feedback system (an interesting case)](https://stackoverflow.com/questions/28364258/proper-database-model-for-a-user-feedback-system-an-interesting-case) — philipxy, Jul 27 '22 at 22:54
[How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) [Re SQL "performance".](https://stackoverflow.com/a/24196511/3404097) — philipxy, Jul 27 '22 at 22:55

score 0 · Answer 1 · answered Jul 28 '22 at 04:26

CREATE TABLE PKTABLE (
    tid int,
    id int,
    PRIMARY KEY (tid, id)
);

CREATE TABLE FKTABLE (
    tid int,
    id int,
    fk_id_del_set_null int,
    fk_id_del_set_default int DEFAULT 0,
    FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES PKTABLE ON DELETE SET NULL (fk_id_del_set_null),
    FOREIGN KEY (tid, fk_id_del_set_default) REFERENCES PKTABLE ON DELETE SET DEFAULT (fk_id_del_set_default)
);

INSERT INTO PKTABLE
    VALUES (1, 0),
    (1, 1),
    (1, 2);

INSERT INTO FKTABLE
    VALUES (1, 1, 1, NULL),
    (1, 2, NULL, 2);

DELETE FROM PKTABLE
WHERE id = 1
    OR id = 2;

SELECT
    *
FROM
    FKTABLE
ORDER BY
    id;

Primary key(tid,id) means both column cannot be NULL.
Unique(tid,id) means it accept (tid null,id null), (tid not null, id null), (tid null, id not null). demo

Other than that, all the same. Primary key will create index, unique contsraint will also create index. see manual

So overall, if you use primary key, you don't need to care about both two column will be null or not. And in pg15 (link), in foreign key you can

FOREIGN KEY (tid, fk_id_del_set_null) REFERENCES PKTABLE ON DELETE SET NULL (fk_id_del_set_null)

Which means that you tenant_id will always exists in all the table related to that tenant_id.

Composite primary key vs a unique constraint in a multi tenant database system

1 Answers1