how do i drop constraint with sqlite3?

Question

I try to drop a constraint if this one exists but i get an error, here is my code and below the error :

return knex.schema.raw(
  `
    ALTER TABLE users
    DROP CONSTRAINT IF EXISTS "users_customer_id_email_unique",
    DROP CONSTRAINT IF EXISTS "users_customer_id_trigram_unique";
  `,
);

My error :

ALTER TABLE users

          DROP CONSTRAINT IF EXISTS "users_customer_id_email_unique",
          DROP CONSTRAINT IF EXISTS "users_customer_id_trigram_unique";
         - SQLITE_ERROR: near "DROP": syntax errorError: SQLITE_ERROR: near "DROP": syntax error

Does this answer your question? [How do I DROP a constraint from a sqlite (3.6.21) table?](https://stackoverflow.com/questions/1884787/how-do-i-drop-a-constraint-from-a-sqlite-3-6-21-table) — forpas, Jan 02 '20 at 16:38
See the **Making Other Kinds Of Table Schema Changes** section at https://www.sqlite.org/lang_altertable.html for the needed steps to recreate the table without those constraints. — Shawn, Jan 02 '20 at 16:52

score 0 · Answer 1 · answered Jan 02 '20 at 21:46

The only DROP statements supported by SQLite are :-

DROP INDEX
DROP TABLE
DROP TRIGGER
DROP VIEW

The ALTER TABLE statement is relatively limited as per :-

As such you need to recreate the table less the constratints.

The following could be the basis for doing that :-

CREATE TABLE IF NOT EXISTS new_users (users_customer_id_email_unique TEXT,users_customer_id_trigram_unique INTEGER, othercolumn);
INSERT INTO new_users SELECT * FROM users;
DROP TABLE IF EXISTS old_users;
ALTER TABLE users RENAME TO old_users; /* could be dropped instead of altered but safer to alter then drop */
ALTER TABLE new_users RENAME TO users;
DROP TABLE IF EXISTS old_users;

Working Example

Perhaps consider the following example based upon your code. Note that this also includes some extras that could be helpful e.g. extracting the sql with UNIQUE (all occurences) removed.

DROP TABLE IF EXISTS old_users;
DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (users_customer_id_email_unique TEXT UNIQUE,users_customer_id_trigram_unique INTEGER UNIQUE, othercolumn);
INSERT INTO users VALUES ('a@email.com',1,'blah'),('b@email.com',2,'blah'),('c@email.com',3,'blah');
/* try to addd some duplicate data (will not be added) */
INSERT OR IGNORE INTO users VALUES ('a@email.com',1,'blah'),('b@email.com',2,'blah'),('c@email.com',3,'blah');
/*RESULT 1 - data in table before removing unique constraint */
SELECT * FROM users;

/* Potentially useful extras that could be used in generating SQL for new table */
SELECT replace(replace(sql,' UNIQUE',''),' unique','') AS newsql FROM sqlite_master WHERE name = 'users';
SELECT * FROM pragma_table_info('users');

/* The actual constraint removal using hard coded replacement table */
CREATE TABLE IF NOT EXISTS new_users (users_customer_id_email_unique TEXT,users_customer_id_trigram_unique INTEGER, othercolumn);
INSERT INTO new_users SELECT * FROM users; /* populate the replacement table */
DROP TABLE IF EXISTS old_users; /* just in case */
ALTER TABLE users RENAME TO old_users; /* could DROP the table here instead of ALTER and subsequent DROP */
ALTER TABLE new_users RENAME TO users;
DROP TABLE IF EXISTS old_users;
/* Add some duplicate rows */
INSERT OR IGNORE INTO users VALUES ('a@email.com',1,'blah'),('b@email.com',2,'blah'),('c@email.com',3,'blah');

/* END RESULTS */
SELECT * FROM users;
SELECT * FROM sqlite_master WHERE name = 'users' OR tbl_name = 'users';
SELECT * FROM pragma_table_info('users');

/* Cleanup Testing Environment */
DROP TABLE IF EXISTS users;

The Results :-

The data in the original table

Extra - Replacement SQL example where UNQIUE is removed

Extra - The column info as per the original table

The data in the table after it has been altered (with duplicated data added)

SQL for the new table (also shows that indexes for UNIQUE columns have been dropped)

The columns of the replacement table

score 0 · Accepted Answer · edited Jan 07 '20 at 13:00

// we first autorise schema modification
await knex.raw('PRAGMA writable_schema = true');
// then drop unique email_customer_id
await knex
  .select()
  .table('sqlite_master')
  .where('type', '=', 'index')
  .andWhere('tbl_name', '=', 'users')
  .andWhere('name', '=', 'users_email_customer_id_unique')
  .del();

//and also drop unique customer_id_trigram
await knex
  .select()
  .table('sqlite_master')
  .where('type', '=', 'index')
  .andWhere('tbl_name', '=', 'users')
  .andWhere('name', '=', 'users_customer_id_trigram_unique')
  .del();

// I move back the autorisation for schema modification to false
return knex.raw('PRAGMA writable_schema = false');

how do i drop constraint with sqlite3?

2 Answers2

Working Example