Ansible postgresql_table模块如何创建外键约束？

你可以根据自身场景选择以下三种官方模块支持的方案，全部满足幂等性要求：

• 建表时同步定义外键：直接在postgresql_table模块的列定义里写完整约束即可，模块不会过滤标准SQL的约束语法，适合新建表的场景：

- name: "Create foo table with foreign key"
  become: yes    
  become_user: postgres
  community.postgresql.postgresql_table:
    db: test_db
    owner: postgres
    name: foo
    columns:
      - id bigserial PRIMARY KEY NOT NULL
      - bar_id bigint CONSTRAINT bar_id_fkey REFERENCES bar(id) MATCH FULL

• 已有表添加外键（推荐）：使用collection自带的postgresql_constraint专用模块管理约束，模块会自动检查约束是否存在，重复执行不会报错，是最符合Ansible规范的方案：

- name: Add foreign key constraint to foo table
  become: yes
  become_user: postgres
  community.postgresql.postgresql_constraint:
    db: test_db
    table: foo
    name: bar_id_fkey
    type: foreign
    columns:
      - bar_id
    references: bar(id)
    match: FULL
    state: present

• 复杂场景执行原生SQL：如果外键逻辑比较特殊，现有模块参数无法覆盖，可以用postgresql_query模块执行自定义ALTER语句，记得加上存在性判断保证幂等：

- name: Check if foreign key already exists
  become: yes
  become_user: postgres
  community.postgresql.postgresql_query:
    db: test_db
    query: "SELECT 1 FROM pg_constraint WHERE conname = 'bar_id_fkey'"
  register: fkey_check

- name: Add foreign key via raw SQL
  become: yes
  become_user: postgres
  community.postgresql.postgresql_query:
    db: test_db
    query: ALTER TABLE foo ADD CONSTRAINT bar_id_fkey FOREIGN KEY (bar_id) REFERENCES bar (id) MATCH FULL;
  when: fkey_check.query_result | length == 0

不建议直接用shell模块拼接psql命令执行，既不好做幂等判断，也容易出现转义问题。

内容的提问来源于stack exchange，提问作者SiOx