CODEWITHSUNDEEP
sqlsqliteconstraints
Encore PTL
Encore PTL

Reputation: 8214

Remove Unique constraint on a column in sqlite database

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 (datasource_name)

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)
);

Upvotes: 11

Views: 15575

Answers (4)

DavidLS7
DavidLS7

Reputation: 11

I was just working through this issue on a small database and found it easier to dump the data as SQL statements, it prints out your tables exactly as they are and also adds the INSERT INTO statements to rebuild the DB.

The .help terminal command shows:

.dump ?OBJECTS? Render database content as SQL

and prints the SQL to the terminal, you can update it in a TXT file. For once off changes and tidying this seems like a reasonable solution albeit a little inelegant

Upvotes: 1

Aditya
Aditya

Reputation: 986

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table_name RENAME TO old_table;

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table_name SELECT * FROM old_table;

COMMIT;

PRAGMA foreign_keys=on;

Source: https://www.techonthenet.com/sqlite/unique.php

Upvotes: 3

phimath
phimath

Reputation: 1422

I just ran into this myself. An easy solution was using DB Browser for SQLite

It let me remove a unique constraint with just a checkbox in a gui.

Upvotes: 7

Michal Kottman
Michal Kottman

Reputation: 16753

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.

Upvotes: 17

Related Questions