【dbt】使用 constraints 定义表主键

dbt 在自动建表的时候，是没有带主键和约束的。如果需要加上的话，可以使用 constraints 配置项。注意：

1. constraints 是 dbt 1.5 新增的配置项。
2. 需要设置 contract.enforced = true，否则 constraints 不生效。
3. 设置 enforced=true 后，必须指定字段的类型。

dbt 支持的约束类型包含：not_null、unique、primary_key、foreign_key、check

测试环境

• dbt 1.5
• 数据库：Postgres 14

建立测试表

1. 测试表 <dbt-project>/models/dim_student.sql

-- id 为主键
with source_data as (
    select 1 as id, '张三' as name union all 
    select 2 as id, '李四' as name union all 
    select 3 as id, '王舞' as name 
)

select * from source_data 

2. 测试表 dbt-project>/models/fct_score.sql

-- id 为主键, student_id 外键
with source_data as (
    select 1 as id, 1 as student_id, 'chinese' as course, 98 as score union all 
    select 2 as id, 1 as student_id, 'math'    as course, 97 as score union all 
    select 3 as id, 1 as student_id, 'english' as course, 96 as score union all 
    select 4 as id, 2 as student_id, 'chinese' as course, 88 as score union all 
    select 5 as id, 2 as student_id, 'math'    as course, 87 as score union all 
    select 6 as id, 2 as student_id, 'english' as course, 86 as score union all 
    select 4 as id, 3 as student_id, 'chinese' as course, 83 as score union all 
    select 5 as id, 3 as student_id, 'math'    as course, 82 as score union all 
    select 6 as id, 3 as student_id, 'english' as course, 81 as score 
)

select * from source_data

单字段主键

使用 dim_student.id 作为表主键，在 schema.yml 配置文件中添加配置如下：

models:
  - name: dim_student
    description: "维度表：学生姓名"
    config:
      contract:
        enforced: true
    constraints:
      - type: primary_key
        columns: [id]
    columns:
      - name: id
        data_type: int
      - name: name
        data_type: varchar(36)

或者，将主键定义放到 columns 属性下，效果是一样的

models:
  - name: dim_student
    description: "维度表：学生姓名"
    config:
      contract:
        enforced: true
    columns:
      - name: id
        data_type: int
        constraints:
          - type: primary_key
      - name: name
        data_type: varchar(36)

执行 dbt run -s dim_student ，检查编译后的建表语句：

  create  table "postgres"."test_exp"."dim_student__dbt_tmp"
  (
    id int,
    name varchar(36),
    primary key (id)
    )
 ;

检查数据库，索引已加

多字段主键

使用 id，name 两个字段构造联合主键，只需要在表 constraints 中，以数组形式指定多个字段即可

models:
 - name: dim_student
   description: "维度表：学生姓名"
   config:
     contract:
       enforced: true
   constraints:
     - type: primary_key
       columns: [id, name]
   columns:
     - name: id
       data_type: int
     - name: name
       data_type: varchar(36)

参考

• dbt官方文档：constraints