Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

NOT NULL constraint on a column when another column has a particular value

create table test (
    col1 varchar(20),
    col2 varchar(20)
)
  1. When col1 has value '1', col2 cannot be null.
  2. When col1 has any other value, col2 can be null.

Is there a way to write a check constraints based on values of particular columns?

like image 812
sat Avatar asked May 25 '14 06:05

sat


People also ask

How do I add a NOT NULL constraint on a column containing null values?

It is possible to add a NOT NULL constraint to an existing table by using the ALTER TABLE statement. In this case, the column_name must not contain any NULL value before applying the NOT NULL constraint.

Which type of constraint does not allow null values in column?

The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

Can a column defined with NOT NULL constraint?

Once a NOT NULL constraint has been defined for a particular column, any insert or update operation that attempts to place a null value in that column will fail.

Which column in a table has always the NOT NULL constraint?

Based on the SQL standard, PRIMARY KEY should always imply NOT NULL . However, SQLite allows NULL values in the PRIMARY KEY column except that a column is INTEGER PRIMARY KEY column or the table is a WITHOUT ROWID table or the column is defined as a NOT NULL column.


1 Answers

You can write a table-level constraint, sure.

CREATE TABLE test (
    col1 VARCHAR(20),
    col2 VARCHAR(20),
    CHECK (col1 != '1' OR col2 IS NOT NULL)
);

Either col1 isn't '1' (and col2 can be anything), or col1 is '1' (and col2 can't be null).

See the third example in the manual.

like image 126
Eevee Avatar answered Oct 14 '22 01:10

Eevee