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

I'm trying to add a NOT NULL constraint in PostgreSQL, but can't find the right syntax. Here is, essentially, what I'm trying to do:

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

and the table I'm trying to modify:

CREATE TABLE olimpic.tb_athlete
(
    athlete_id CHAR(7) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    country CHAR(3) NOT NULL,
    substitute_id CHAR(7),
    FOREIGN KEY (substitute_id) REFERENCES olimpic.tb_athlete(athlete_id)
);

I have already deleted or set default values to the column country where the value is 'ESP', with this code being an example:

DELETE FROM olimpic.tb_athlete
where substitute_id is NULL and country = 'ESP';

but I'm still getting the following error:

ERROR: ERROR: check constraint 'soloESP' on table tb_athlete is violated by some row SQL state: 23514

Any help you could give me as to how to proceed would be greatly appreciated.

CodePudding user response：

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 can analyze what is really happening in your case. Please, consider editing the question to add the above