drop or alter primary key constraint for a streaming table in delta live tables

[UM1]

I have a dlt streaming table with a primary key constraint defined through the schema definitions. I have redeployed the same dlt pipeline with the same target. When attempting to run the pipeline, I get the error,  ErrorClass=RESOURCE_ALREADY_EXISTS. Constraint with name `pk_constraint_name` already exists, choose a different name.

I can't find the constraint in information_schema.constraint_table_usage, and thought that the constraint is deleted, but the same error persists when rerunning with the constraint.

Is there a way to alter or drop the schema constraint? It seems to remember it even after running full refresh without constraint and then putting the same constraint back in.

[susanne]

Have you found a solution/workaround for this?
I am facing the same problem and a solution would be greatly appreciated!

[susanne]

For me the problem was solved when I defined the schema in dlt as following:

@Dlt.table(
name=f"{target_table}",        
schema = """userid STRING,                    
                    contract_no INT,
                   CONSTRAINT pk_<table_name> PRIMARY KEY(userid)""",    
)

instead of using this:

schema = """userid STRING PRIMARY KEY,                    
                    contract_no INT"""

[Sidhant07]

The `RESOURCE_ALREADY_EXISTS` error you're encountering suggests that the primary key constraint `pk_constraint_name` already exists in the target delta table. This constraint may have been created during a previous deployment of the DLT pipeline or during a manual schema modification.

To resolve this issue, you can try dropping the primary key constraint using the `ALTER TABLE` command in SQL. Here's an example SQL command that you can use to drop the constraint:
```sql
ALTER TABLE target_table DROP CONSTRAINT pk_constraint_name
```
Replace `target_table` with the name of your target delta table and `pk_constraint_name` with the name of the primary key constraint that you want to drop.

If you're unable to find the constraint in the `information_schema.constraint_table_usage` table, it's possible that the constraint was created using a different name or in a different schema. In this case, you can try querying the `information_schema.table_constraints` table to find the constraint:
```sql
SELECT * FROM information_schema.table_constraints WHERE constraint_name = 'pk_constraint_name'
```
Replace `pk_constraint_name` with the name of the primary key constraint that you want to drop. This query should return a row for each table that has a constraint with the specified name.

Once you've identified the constraint, you can use the `ALTER TABLE` command to drop it as described above.

If you're still encountering issues with the primary key constraint, you can try dropping and recreating the target delta table. This will remove all constraints and data from the table, allowing you to start fresh. However, be aware that this will also delete any data that's currently stored in the table, so proceed with caution.

[kranthi2]

In our case, we dropped a DLT pipeline and recreated the pipeline. Dropping DLT pipeline dropped the tables with constraints. After recreating the DLT pipeline and rerunning the job, it throws this error. Now, the table is not available and the constraint is not available in meta data(information_schema.tables has no tables associated with dlt  and no data in information_schema.table_constraints) . Yet, it throws this error. Any help would be much appreciated. Thank you