Search code examples
sqlpostgresqlpgadmin-4

How to set a specific field to not null?

I have a table where two columns show the country and the passport respectively both being type varchar, it also have other columns but are not relevant for the problem, and I want to add a constraint where a specific country may not have a passport. For example, only the country Italy can have a passport or can be set as null and the other countries must have a passport.

I have tried this code:

alter table profesor alter column passport set not null where country != 'Italy'

Which drops the next error:

ERROR: syntax error at or near "where" 
LINE 1: ...able profesor alter column passport set not null where country... 
                                                             ^ 
SQL state: 42601 
Character: 58
Solution

  • I think you want a check constraint, not a not null constraint:

    alter table profesor
    add constraint chk_profesor_passport
        check (passport is not null or country = 'Italy');