Hello all,
I have written a small piece of code to have a check constraint with function that validates a country. I am aware there are other ways of doing this.. Any reason why the check constraint is failing ?
create table address(
Fname varchar2(30),
Lname varchar(30),
Street varchar(30),
City varchar(30),
Country varchar(30)
);
Create table country(
country varchar2(30),
continent varchar2(20)
);
insert into country values ('CHINA','ASIA');
insert into country values ('EGYPT','AFRICA');
CREATE OR REPLACE FUNCTION fn_validate_country (
V_Country varchar2
)
RETURN boolean
AS
VAR_COUNTRY varchar2(30);
BEGIN
SELECT count(Country) into VAR_COUNTRY FROM Country WHERE country = V_country;
if VAR_COUNTRY > 0
then
return TRUE;
else
return FALSE;
end if;
END;
/
ALTER TABLE Address
ADD CONSTRAINT CK_Country
CHECK (fn_validate_country(country) ='TRUE')
SQL>
ALTER TABLE Address
ADD CONSTRAINT CK_Country
CHECK (fn_validate_country(country) ='TRUE')SQL> 2 3 ;
CHECK (fn_validate_country(country) ='TRUE')
*
ERROR at line 3:
ORA-00904: "FN_VALIDATE_COUNTRY": invalid identifier
Thanks