CODEWITHSUNDEEP
sqloracle-databasemany-to-many
m.qayyum
m.qayyum

Reputation: 411

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.

Upvotes: 0

Views: 200

Answers (2)

onedaywhen
onedaywhen

Reputation: 57063

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;

Upvotes: 2

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Usual manouever for this would require primary keys for student and teacher tables, and then foreign keys for T_Id and S_id to Teachers and Students from Teacher_Student.

When you've done that, inserting to student and teacher, would only check uniqueness of their keys, i.e , you can't have two student's with an id of 1.

Inserting to Teacher_Student would check uniqueness of the relationship, and that the inserted ids exist in their respective tables.

PS abbreviating database object names is a very objectionable habit.

Upvotes: 1

Related Questions