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');
Advertisement
Answer
This should work :
CONSTRAINT check_substitute_id CHECK (country != 'ESP' OR substitute_id IS NOT NULL)