Alert table and add constraint not null based on another column condition-CodePudding

I have this table:

CREATE TABLE tb_athlete (
    athlete_id CHARACTER(7) NOT NULL,
    name CHARACTER VARYING(50)  NOT NULL,
    country CHARACTER(3)  NOT NULL,
    substitute_id CHARACTER (7),
    CONSTRAINT PK_tb_athlete PRIMARY KEY(athlete_id),
    CONSTRAINT FK_athlete_substitute  FOREIGN KEY (substitute_id) REFERENCES tb_athlete(athlete_id)
    );

I want to add a restriction thanks to which the substitute ID cannot be NULL where athletes country is "ESP"

I have tried the following, but I am not sure where to place the WHERE condition:

ALTER TABLE tb_athlete 
ALTER COLUMN
subsitute_id
CHARACTER(7) NOT NULL
WHERE country LIKE 'ESP';

Any ideas on how to solve this? (:

I was also trying to use CHECK, but I am not sure if I implement it correctly

ALTER TABLE tb_athlete 
ADD CONSTRAINT check_substitute_id CHECK (substitute_id  IS NULL OR country LIKE 'ESP');

CodePudding user response：

This should work :

CONSTRAINT check_substitute_id CHECK ((country = 'ESP' AND substitute_id IS NOT NULL) OR country != 'ESP')

demo