Alter 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' OR substitute_id IS NOT NULL)

demo

CodePudding user response：

I had the same problem before I solved it like this :

ALTER TABLE tb_athlete  ADD CONSTRAINT contraint check (tb_athlete.substitute_id is not null or tb_athlete.country LIKE 'ESP');