How to Add a Not Null Constraint on Column Containing Null Values

How to add a not null constraint on column containing null values

You can add an unvalidated constraint - it will not look at existing rows, but it will be checked for any new or updated rows.

ALTER TABLE mytable MODIFY mycolumn NOT NULL NOVALIDATE;

Just be aware that you won't be able to update an existing row unless it satisfies the constraint.

Also, be aware of the downside that the optimizer will not be able to take advantage of this constraint in making its plans - it has to assume that some rows may still have a null.

MySql: Adding not null constraint to column with null values passes?

First check:

SELECT @@sql_mode;

You probably get:

STRICT_TRANS_TABLES/STRICT_ALL_TABLES

UP env :   -------
DOWN env:   STRICT_..._TABLES

The difference is Error vs Warning:

Modes:

Strict mode controls how MySQL handles invalid or missing values in
data-change statements such as INSERT or UPDATE. A value can be
invalid for several reasons. For example, it might have the wrong data
type for the column, or it might be out of range. A value is missing
when a new row to be inserted does not contain a value for a non-NULL
column that has no explicit DEFAULT clause in its definition. (For a
NULL column, NULL is inserted if the value is missing.)

SqlFiddleDemo

The correct way to solve it is first to update NULL to empty string before altering schema.

CREATE TABLE test_table(primary_key INT AUTO_INCREMENT PRIMARY KEY,
                        short_char CHAR(100));

INSERT INTO test_table(short_char)
VALUES (NULL);

UPDATE test_table
SET short_char = ''
WHERE short_char IS NULL;

ALTER TABLE test_table modify short_char char(100) not null;

SqlFiddleDemo2

I recommend to use VARCHAR(100) instead of CHAR(100).

Add a not null constraint to a table in which a column has a certain value in PostgreSQL

Do you realize that the constraint you're trying to add does not allow any rows with the country field other than 'ESP'? You say you want two conditions simultaneously (because you use the AND operator): each row must have country = 'ESP' AND non-null substitute_id

I believe what you wanted is

ALTER TABLE olimpic.tb_athlete
ADD CONSTRAINT soloESP CHECK(country != 'ESP' OR substitute_id IS NOT NULL);

This constraint will ensure that if country = 'ESP' then substitute_id must be non-null. For other countries both null and non-null values of substitute_id are valid.

But the above is only a guess because you provided neither your database's schema, nor meanings of the fields, nor the error text in English, nor the data stored in your database so that we could analyze what is really happening in your case. Please, consider editing the question to add the above

how to add not null constraint for a column after dropping it if the table contains data

As described in Pg docs you can do something like this

update table_name set columnname='fill null data' where columnname is null;
ALTER TABLE table_name ALTER COLUMN columnname SET NOT NULL;

ALTER TABLE table_name ALTER COLUMN columnname SET default ' ';

How to force NOT NULL constraint when inserting, without affecting existing null values?

Instead of making the column NOT NULL (which won't work), you could add a check constraint that makes sure the value isn't NULL and then apply the check constraint with a NOCHECK option. Here's an example:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.Test;

CREATE TABLE dbo.Test
(
    TestID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_dbo_Test PRIMARY KEY,
    TestName varchar(50) NULL
);
GO

INSERT dbo.Test (TestName)
VALUES ('Fred'), (NULL), ('Mary');
GO

ALTER TABLE dbo.Test
WITH NOCHECK ADD CONSTRAINT CK_dbo_Test_TestName_not_null
CHECK (TestName IS NOT NULL);
GO

-- OK
INSERT dbo.Test (TestName)
VALUES ('Mia');
GO

-- Not OK
INSERT dbo.Test (TestName)
VALUES (NULL);
GO

How to add a not null constraint to an already existing column with null values?

Your ALTER command didn't work, the Null column still says YES. Your ALTER command syntax looks just fine, it should have worked. Check your typing and try again.

Is your customer ID really just a char?

How to add a new column on NOT NULL constraint

If the new row is supposed to be NOT NULL, add a DEFAULT clause to the column definition:

ALTER TABLE tab
ADD COLUMN Col3 INTEGER NOT null
DEFAULT 0;

Alternatively, omit the NOT NULL, fill the new column with UPDATE, then change the column to be NOT NULL:

ALTER TABLE tab
ALTER col3 SET NOT NULL;

After an UPDATE on the whole table, you should run VACUUM (FULL) tab to get rid of the bloat.

---

Related Topics