sqloracle-databasemany-to-many

How to insert a record in many to many relationship tables?


For exmple i have two tables

A

create table teachers(
id number(4) primary key,
name varchar(20)
);

B

create table students(
id number(4) primary key,
name varchar(20)
);

and a third table

AB

create table Teacher_Student(
T_Id number(4),
S_Id number(4)
);

and their relationship

alter table teacher_student
add constraint s_t_pk Primary key(T_Id, S_Id);

Is this relationship is created correctly? and what would be the insertion query if i want to add a new student or a teacher.


Solution

  • Suggestion: also add referential integrity constraints:

    alter table teacher_student
       add constraint s_t_fk_t foreign key (T_Id)
       references teachers (id)
       on delete cascade
       on update cascade;
    
    alter table teacher_student
       add constraint s_t_fk_s foreign key (S_Id)
       references students (id)
       on delete cascade
       on update cascade;