snowflake_table_constraint is not working with primary key

[krishreddy56]

Terraform CLI and Provider Versions

Terraform: 1.7.2 Provider: 0.87

Terraform Configuration

resource "snowflake_table" "test" {
  name     = "TEST"
  schema   = "TEST_SCHEMA"
  database = "TEST_DATABASE"

    column {
        name: "USER_ID",
        type: "VARCHAR(255)"
    }

    column {
        name: "TEST_ID",
        type: "VARCHAR(255)"
    }

    column {
        name: "COLUMN_A",
        type: "VARCHAR(50)"
    }

    column {
        name: "COLUMN_B",
        type: "VARCHAR(255)"
    }

    column {
        name: "COLUMN_C",
        type: "VARCHAR(255)"
    }

  lifecycle {
    prevent_destroy = true
  }
}

resource "snowflake_table_constraint" "test_pk" {
  name     = "test_primary_key"
  type     = "PRIMARY KEY"
  table_id = snowflake_table.test.qualified_name
  columns  = ["USER_ID"]
}

Expected Behavior

primary key to be created

Actual Behavior

terraform apply passes but I don't see any primary key on the table. Verified by doing SHOW PRIMARY KEYS IN TABLE TEST;

Steps to Reproduce

1. terraform apply

How much impact is this issue causing?

High

Logs

No response

Additional Information

No response

[sfc-gh-asawicki]

Hey @krishreddy56. Thanks for reaching out to us.

I will try to reproduce it in the next days and will get back to you.

[sfc-gh-asawicki]

Hey @krishreddy56. I finally had time to verify the issue.

It is connected with #2629. Long story short:

1. The defaults in this resource are incorrect and the resulting SQL queries add additional parameters.
2. Depending on the combinations (https://docs.snowflake.com/en/sql-reference/sql/create-table-constraint#properties-for-all-keys, https://docs.snowflake.com/en/sql-reference/constraints-properties), primary key is created or not\in Snowflake (https://docs.snowflake.com/en/sql-reference/constraints-properties#non-default-values-for-the-enable-and-validate-constraint-properties).

I was able to show the primary key by adding:

    enable = false
    deferrable = false

to your config. However, I don't know what properties combination you want to have set on your constraint. To understand these properties better, please reach out to Snowflake support/your account manager.

We will change the defaults and improve the logic of this resource as part of the resources redesign (https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#supporting-all-snowflake-ga-features).

[imanebenomar]

Hello @sfc-gh-asawicki , I have the same problem, In terraform it says that it created my primary keys, but there is nothing on my tables in snowflake. I tried combining multiple properties values but nothing worked.

#create tables
resource "snowflake_table" "my_table" {
  database = snowflake_database.bronze_layer.name
  for_each = module.common_vars.SNOWFLAKE_BRONZE_TABLES

  name = upper(each.value.table_name)
  schema = each.value.schema
  data_retention_time_in_days = 0
  change_tracking = false
  comment = ""
  cluster_by = []

  dynamic "column" {
    for_each = jsondecode(file("${path.module}/tableschemas/bronze_layer/${each.value.table_name}.json"))
    content {
      name     = column.value.name
      type     = column.value.type
      nullable = column.value.nullable != null ? column.value.nullable : false
      masking_policy = ""
      comment  = column.value.comment != null ? column.value.comment : ""
      collate =  ""
    }
  }
}

resource "snowflake_table_constraint" "primary_key" {
  for_each = module.common_vars.SNOWFLAKE_BRONZE_TABLES
  name     = "PK_${upper(each.value.table_name)}"
  type     = "PRIMARY KEY"
  #table_id = "${snowflake_database.bronze_layer.name}.${each.value.schema}.${upper(each.value.table_name)}"
  table_id = snowflake_table.my_table[each.key].id
  columns  =  each.value.primary_keys
  deferrable  = false
  enable  = true
  enforced =  false
  validate = false
  initially = "immediate"
  rely = true
}

Can you help me fix this problem please ?

also, when I try removing the properties from my code like that :

resource "snowflake_table_constraint" "primary_key" {
  for_each = module.common_vars.SNOWFLAKE_BRONZE_TABLES
  name     = "PK_${upper(each.value.table_name)}"
  type     = "PRIMARY KEY"
  #table_id = "${snowflake_database.bronze_layer.name}.${each.value.schema}.${upper(each.value.table_name)}"
  table_id = snowflake_table.my_table[each.key].id
  columns  =  each.value.primary_keys
  enable  = true
  rely = true
}

I get this errors : Error: Provider produced invalid plan │ │ Provider "registry.terraform.io/snowflake-labs/snowflake" planned an invalid value for module.snowflake.snowflake_table_constraint.primary_key["subscriptions"].initially: planned value cty.StringVal("DEFERRED") for a │ non-computed attribute. │ │ This is a bug in the provider, which should be reported in the provider's own issue tracker. ╵ ╷ │ Error: Provider produced invalid plan │ │ Provider "registry.terraform.io/snowflake-labs/snowflake" planned an invalid value for module.snowflake.snowflake_table_constraint.primary_key["companies"].deferrable: planned value cty.True for a non-computed attribute. │ │ This is a bug in the provider, which should be reported in the provider's own issue tracker. ╵ ╷ │ Error: Provider produced invalid plan │ │ Provider "registry.terraform.io/snowflake-labs/snowflake" planned an invalid value for module.snowflake.snowflake_table_constraint.primary_key["companies"].initially: planned value cty.StringVal("DEFERRED") for a non-computed │ attribute. │ │ This is a bug in the provider, which should be reported in the provider's own issue tracker.

[sfc-gh-asawicki]

Hey @imanebenomar. Can you try only with:

enable = false
deferrable = false

so in your case:

resource "snowflake_table_constraint" "pk" {
        for_each = module.common_vars.SNOWFLAKE_BRONZE_TABLES
        name     = "PK_${upper(each.value.table_name)}"
        type     = "PRIMARY KEY"
        #table_id = "${snowflake_database.bronze_layer.name}.${each.value.schema}.${upper(each.value.table_name)}"
        table_id = snowflake_table.my_table[each.key].id
        columns  =  each.value.primary_keys
        deferrable  = false
        enable  = false
}

[imanebenomar]

Hello @sfc-gh-asawicki ! I updated my terraform version and tried with enable = false and deferrable = false. I can see now my constraints in table_constraints. but with the enable=false it means that my primary_key is not enabled on my table ? like if there is no PK constraint on my table ? I am not quit sure what enable means. Thank you for your help.

[sfc-gh-asawicki]

Please check this link: https://docs.snowflake.com/en/sql-reference/constraints-properties#non-default-values-for-the-enable-and-validate-constraint-properties. For more information about the inner workings of table constraints, please reach out to your Snowflake account manager, this is outside my field of expertise.

[imanebenomar]

Okay ! thank you for your reactivity and your help.

[ZhizhenWang]

Hi @sfc-gh-asawicki we also faced this constraint not being created issue now. After confirmed with snowflake support, NOT DEFERRABLE and DISABLE are default value, could in terraform also changed their value as default? Otherwise, everyone without explicitly declaring the value will cause not creating the constraint.

[sfc-gh-asawicki]

Hey. Yes, as part of:

We will change the defaults and improve the logic of this resource as part of the resources redesign (https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#supporting-all-snowflake-ga-features).