CODEWITHSUNDEEP
oracle-databasestored-proceduresoracle12c
soul king
soul king

Reputation: 33

Procedure on update data

I have this tables and 1 procedure on update data...

CREATE sequence DIR_ID_SEQ;
create table DIRECTORS (
ID_DIR INTEGER DEFAULT DIR_ID_SEQ.NEXTVAL NOT NULL,
DIRECTOR_CODE INTEGER DEFAULT DIR_ID_SEQ.NEXTVAL NOT NULL,
SURNAME VARCHAR2(200) NOT NULL,
NAME VARCHAR2(200) NOT NULL,
COUNTRY VARCHAR2(200) NOT NULL,
COUNTRY_CODE INTEGER,
CONSTRAINT  PK_ID_DIR PRIMARY KEY (ID_DIR)
)
TABLESPACE TBS_PERM_KINO;
----------
CREATE sequence COUNTRY_ID_SEQ;
CREATE TABLE COUNTRY (
ID_COUNTRY INTEGER DEFAULT COUNTRY_ID_SEQ.NEXTVAL NOT NULL,
NAME VARCHAR2(200) NOT NULL,
COUNTRY_CODE INTEGER,
CONSTRAINT  PK_ID_COUNTRY PRIMARY KEY (ID_COUNTRY)
)
TABLESPACE TBS_PERM_KINO;
----------
alter table DIRECTORS add constraint FK_DIR_CODE_REF_ID_COUNTRY foreign key (DIRECTOR_CODE) references COUNTRY (ID_COUNTRY);
----------
create or replace procedure UpdateDirector(P_ID DIRECTORS.ID_DIR%TYPE,P_SURNAME DIRECTORS.SURNAME%TYPE,P_NAME IN DIRECTORS.NAME%TYPE,P_COUNTRY IN DIRECTORS.COUNTRY%TYPE,P_COUNTRY_CODE IN DIRECTORS.COUNTRY_CODE%TYPE)
is
begin
    UPDATE DIRECTORS SET ID_DIR = P_ID,
        SURNAME = P_SURNAME,
            NAME = P_NAME, COUNTRY = P_COUNTRY, 
                COUNTRY_CODE = P_COUNTRY_CODE 
                    WHERE DIRECTORS.ID_DIR = P_ID;
    exception
    when others 
    then DBMS_OUTPUT.put_line(sqlerrm);
end;
----------
BEGIN 
UpdateDirector(7,'TEST','TESTING','MEXICO',2);
END;

when i run UpdateDirector, with country and country_code which is not in COUNTRY table it works fine, but it shouldn't, i need to change my procedure, so that it checks for the validity of the input COUNTRY and CONTRY_CODE, can you tell me how i can do this?

Upvotes: 0

Views: 36

Answers (1)

MT0
MT0

Reputation: 168041

Add a referential constraint:

ALTER TABLE DIRECTORS
  ADD CONSTRAINT FK_COUNTRY_CODE_REF_ID_COUNTRY foreign key (COUNTRY_CODE)
    REFERENCES COUNTRY (ID_COUNTRY);

Then do not store the COUNTRY column in the DIRECTORS table as that duplicates data that is stored elsewhere in the COUNTRY table, violates 3NF and as the data changes potentially leads to the two tables becoming out-of-sync.

If you want to display the country's name then you can use a JOIN to get that data from the country table as it is needed.

Upvotes: 1

Related Questions