Error when inserting record into a database Oracle SQLPLUS

Question

I am new to this, so this can be silly, but..

I have 3 created tables:

  create table faculty (id_faculty number(2) Primary Key, //id , 2 digits, primary key
  faculty_name varchar2(30) constraint f_name Not Null, // name, up to 30 symbols, not null
  dean varchar2(20), // dean, up to 20 symbols
  telephone varchar2(8)); //number, up to 8 symbols

create table student (id_student number(3) Primary Key, //id, 3 digits, primary key
   student_name varchar(20) constraint s_name Not Null, // name, up to 20 symbols, not null
   student_surname varchar(20) constraint s_surname Not Null, // name, up to 20 symbols, not null
   course_year number(1) constraint s_course_year Check(course_year>0 and course_year<=6), // number, 1 digit, in range 1-6
   faculty_id number(2), // id, 2 digits
   constraint s_fkey Foreign key (Faculty_ID) References faculty(ID_faculty)); // foreign key to table faculty to id_faculty

create table money (id_payout number(4) Primary Key,
  student_id number(3),
  constraint m_fkey Foreign key (student_ID) References student(ID_student),
  payout_date date constraint p_date Not Null,
  stipend number(5,2) Check(stipend<151),
  compensation number(5,2));

Then I created view:

create or replace view stud_stip AS
 Select f.faculty_name, student_surname, student_name, s.course_year,
 m.stipend, m.payout_date
 from
 faculty f inner join student s on f.id_faculty=s.faculty_id
 inner join money m on m.student_id=s.id_student
 where
 m.payout_date = ( select distinct max(payout_date)
 from money
 where money.student_id=money.student_id)
 with check option;

This is how the view looks

Then I wanted to add information to a view:

insert into stud_stip values ('Partikas tehnologiju', 'Lapa', 'Jana', 2, 120, '03.12.1998');

This is the error I got:

ORA-01779: cannot modify a column which maps to a non key-preserved table

I have googled all over the internet and did not solve my error, please don't send me other topics about this question, because I probably did read it.

I will be very grateful for the answers. Thank you in advance.

Answer 1

The way to do it is to create an instead of trigger on a view which would - in turn - insert rows into appropriate tables.

As all ID columns are primary keys and you didn't explain how you populate them, I'll do it using a sequence, in the same instead of trigger.

SQL> create sequence seqs;

Sequence created.

SQL> create or replace trigger trg_stud_stip
  2    instead of insert on stud_stip
  3    for each row
  4  begin
  5    insert into faculty (id_faculty, faculty_name)
  6      values (seqs.nextval, :new.faculty_name);
  7
  8    insert into student (id_student, student_name, student_surname, course_year)
  9      values (seqs.nextval, :new.student_name, :new.student_surname, :new.course_year);
 10
 11    insert into money (id_payout, stipend, payout_date)
 12      values (seqs.nextval, :new.stipend, :new.payout_date);
 13  end;
 14  /

Trigger created.

Testing (don't insert strings into DATE datatype columns!):

SQL> insert into stud_stip
  2    (faculty_name, student_surname, student_name, course_year, stipend, payout_date)
  3    values
  4    ('Partikas tehnologiju', 'Lapa', 'Jana', 2, 120, date '1998-12-03');

1 row created.

SQL> select * from faculty;

ID_FACULTY FACULTY_NAME                   DEAN                 TELEPHON
---------- ------------------------------ -------------------- --------
         1 Partikas tehnologiju

SQL> select * from student;

ID_STUDENT STUDENT_NAME         STUDENT_SURNAME      COURSE_YEAR FACULTY_ID
---------- -------------------- -------------------- ----------- ----------
         2 Jana                 Lapa                           2

SQL> select * from money;

 ID_PAYOUT STUDENT_ID PAYOUT_DAT    STIPEND COMPENSATION
---------- ---------- ---------- ---------- ------------
         3            03.12.1998        120

SQL>

It works. Now that you know how, improve it if necessary.