CODEWITHSUNDEEP
sqlite
user3427343
user3427343

Reputation: 31

How to change a column from NULL to NOT NULL in SQLite3?

For example, there is a table named Table1.

CREATE TABLE Table1 (
    aa INT PRIMARY KEY,
    bb INT
);

I wanna change bb to not null.But sqlite dont support ALTER MODIFY. So what i is :

CREATE TABLE sqlitestudio_temp_table AS SELECT *
                                          FROM Table1;

DROP TABLE Table1;

CREATE TABLE Table1 (
    aa INT PRIMARY KEY,
    bb INT NOT NULL
);

INSERT INTO Table1 (
                       aa,
                       bb
                   )
                   SELECT aa,
                          bb
                     FROM sqlitestudio_temp_table;

DROP TABLE sqlitestudio_temp_table;

And there will be an error:

Could not commit table structure. Error message: NOT NULL constraint failed: Table1.bb

Upvotes: 0

Views: 4699

Answers (1)

CL.
CL.

Reputation: 180030

This is the correct way to make a column NOT NULL.

In this case, it does not work because the column already contains NULL values. So you have to either remove those rows:

DELETE FROM Table1 WHERE bb IS NULL;

or replace the NULL values with any other value:

UPDATE Table1 SET bb = ... WHERE bb IS NULL;

Upvotes: 3

Related Questions