RELACIONAMENTO 1 PARA 1 NO POSTGRESQL PARA REAL

Posted on: January 07, 2021 03:54 PM

Posted by: Renato

Categories: postgresql sql

Views: 56

Antes o truque simples e óbvio:

1

2

3

4

5

6

7

8

9

10

11

12

CREATE TABLE UserProfiles (

        UProfileID BIGSERIAL PRIMARY KEY,

...

);

 

CREATE TABLE Users (

        UID BIGSERIAL PRIMARY KEY,

        UProfileID int8 NOT NULL,

...

        UNIQUE(UProfileID),

        FOREIGN KEY(UProfileID) REFERENCES Users(UProfileID)

);

Você coloca uma restrição única em uma coluna referenciada e está tudo bem. Mas então um dos leitores percebeu que esta é a relação 1-para- (0..1), não uma verdadeira relação 1-para-1. E ele estava absolutamente correto.

Truque muito mais simples usando recursos modernos ou PostgreSQL.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

BEGIN;

 

CREATE TABLE uProfiles (

        uid int8 PRIMARY KEY,

        payload jsonb NOT NULL

);

 

CREATE TABLE Users (

        uid int8 PRIMARY KEY,

        uname text NOT NULL,

        FOREIGN KEY (uid) REFERENCES uProfiles (uid)

);

 

ALTER TABLE uProfiles

    ADD FOREIGN KEY (uid) REFERENCES Users (uid);

 

INSERT INTO Users VALUES (1, 'Renato Lucena');

 

INSERT INTO uProfiles VALUES (1, '{}');

 

COMMIT;

...

Criamos duas tabelas e referenciamos uma à outra usando as mesmas colunas em ambas as maneiras. Além disso, nesse modelo ambas as nossas chaves estrangeiras são indexadas automaticamente!

Parece legítimo, mas a execução deste script produzirá o erro:

SQL Error [23503]: ERROR: insert or update on table "users" 
   violates foreign key constraint "users_uid_fkey"
Detail: Key (uid)=(1) is not present in table "uprofiles".

E essa era a armadilha que impedia as soluções fáceis anos atrás.

 

Mas agora temos restrições DEFERRABLE:

Isso controla se a restrição pode ser adiada. Uma restrição que não pode ser adiada será verificada imediatamente após cada comando. A verificação das restrições que são adiadas pode ser adiada até o final da transação (usando o comando SET CONSTRAINTS). NÃO DEFERRABLE é o padrão. Atualmente, apenas as restrições UNIQUE, PRIMARY KEY, EXCLUDE e REFERENCES (chave estrangeira) aceitam esta cláusula | (foreign key) constraints accept this clause.. As restrições NOT NULL e CHECK não são postergáveis. Observe que as restrições diferíveis não podem ser usadas como árbitros de conflito em uma instrução INSERT que inclui uma cláusula ON CONFLICT DO UPDATE.

Portanto, o truque é não verificar a consistência dos dados até o final da transação. Vamos tentar!

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

BEGIN;

 

CREATE TABLE uProfiles (

        uid int8 NOT NULL PRIMARY KEY,

        payload jsonb NOT NULL

);

 

CREATE TABLE Users (

        uid int8 NOT NULL PRIMARY KEY,

        uname text NOT NULL

);

 

ALTER TABLE Users

        ADD FOREIGN KEY (uid) REFERENCES uProfiles (uid)

                DEFERRABLE INITIALLY DEFERRED;

 

ALTER TABLE uProfiles

        ADD FOREIGN KEY (uid) REFERENCES Users (uid)

                DEFERRABLE INITIALLY DEFERRED;

 

INSERT INTO Users VALUES (1, 'Renato Lucena');

 

INSERT INTO uProfiles VALUES (1, '{}');

 

COMMIT;

Neat! Works like a charm!

1

SELECT * FROM Users, uProfiles;

uid|uname      |uid|payload|
---|-----------|---|-------|
  1|Renato Lucena|  1|{}     |

 

Do meu ponto de vista, este método pode ajudar a dividir tabelas largas em várias estreitas, onde algumas colunas são muito lidas.

Fonte: 

- https://www.cybertec-postgresql.com/en/1-to-1-relationship-in-postgresql-for-real/

- https://stackoverflow.com/questions/15037349/creating-postgresql-tables-relationships-problems-with-relationships-one-t

https://www.postgresql.org/docs/6.5/sql22234.htm

Share
About Author

Renato

Developer

Add a Comment
Comments 0 Comments

No comments yet! Be the first to comment