在sqlite数据库中的列上删除唯一约束 [英] Remove Unique constraint on a column in sqlite database

问题描述

我正在尝试删除sqlite列上的UNIQUE约束，但我没有要删除约束的名称.如何找到要删除的UNIQUE约束名称的名称.

I am trying to remove a UNIQUE constraint on a column for sqlite but I do not have the name to remove the constraint. How can I find the name of the UNIQUE constraint name to remove it.

下面是我要删除约束的表的架构

Below is the schema I see for the table I want to remove the constraint

UNIQUE(数据源名称)

sqlite> .schema datasources
CREATE TABLE "datasources" (
    created_on DATETIME NOT NULL, 
    changed_on DATETIME NOT NULL, 
    id INTEGER NOT NULL, 
    datasource_name VARCHAR(255), 
    is_featured BOOLEAN, 
    is_hidden BOOLEAN, 
    description TEXT, 
    default_endpoint TEXT, 
    user_id INTEGER, 
    cluster_name VARCHAR(250), 
    created_by_fk INTEGER, 
    changed_by_fk INTEGER, 
    "offset" INTEGER, 
    cache_timeout INTEGER, perm VARCHAR(1000), filter_select_enabled BOOLEAN, params VARCHAR(1000), 
    PRIMARY KEY (id), 
    CHECK (is_featured IN (0, 1)), 
    CHECK (is_hidden IN (0, 1)), 
    FOREIGN KEY(created_by_fk) REFERENCES ab_user (id), 
    FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id), 
    FOREIGN KEY(cluster_name) REFERENCES clusters (cluster_name), 
    UNIQUE (datasource_name), 
    FOREIGN KEY(user_id) REFERENCES ab_user (id)
);

推荐答案

SQLite仅支持有限的ALTER TABLE ，因此您无法使用ALTER TABLE移除约束.您可以删除"列的方法是重命名表，使用具有唯一UNIQUE约束之外的相同架构的新表，然后将所有数据插入到新表中.此过程记录在ALTER TABLE文档的进行其他类型的表架构更改部分.

SQLite only supports limited ALTER TABLE, so you can't remove the constaint using ALTER TABLE. What you can do to "drop" the column is to rename the table, create a new table with the same schema except for the UNIQUE constraint, and then insert all data into the new table. This procedure is documented in the Making Other Kinds Of Table Schema Changes section of ALTER TABLE documentation.

这篇关于在sqlite数据库中的列上删除唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！