Junction table data insertion

• Question

• 

  

  0

  Sign in to vote

  Hi, here is the script of my database. The problem is I am inserting data into the tables, and I have to insert data even in the junction tables. Junction tables don't get the data (Foreign keys) auto inserted when their adjacent tables are filled.

  DataBase Creation:

           

  CREATE DATABASE SCHOOL
  
  USE SCHOOL
  
  
  CREATE TABLE Class 
  (
  C_ID numeric(10) NOT NULL,
  C_Name  varchar(20) NOT NULL,
  C_Level numeric(10) NOT NULL,
  C_Strength numeric(20) NOT NULL,
  CONSTRAINT CLS_PK PRIMARY KEY(C_ID)
  )
  
  CREATE TABLE Student
  (
   S_ID numeric(10) NOT NULL,
   C_ID numeric(10) NOT NULL,
   S_FirstName varchar(10)NOT NULL,
   S_LastName varchar(10) NOT NULL,
   S_DOB date NOT NULL,
   S_FatherName varchar(20),
   S_Guardian varchar(20) NOT NULL,
   S_City varchar(10) NOT NULL,
   S_Street varchar(10) NOT NULL,
   S_HouseNo varchar(10) NOT NULL,
   S_Contact varchar(12) NOT NULL
  
   CONSTRAINT STD_PK PRIMARY KEY(S_ID),
   CONSTRAINT FK_CLS_STD FOREIGN KEY(C_ID) REFERENCES Class(C_ID)
  )
  
  CREATE TABLE Course
  (
    CRS_ID numeric(10) NOT NULL,
    C_ID numeric(10) NOT NULL,
    C_Title varchar(20) NOT NULL,
    C_Info varchar (100),
  
    CONSTRAINT PK_COURSE PRIMARY KEY(CRS_ID),
    CONSTRAINT FK_CLS_CRS FOREIGN KEY(C_ID) REFERENCES Class(C_ID)
  )
  
  CREATE TABLE Teacher 
  (
  T_ID numeric(10) NOT NULL,
  T_FirstName varchar(20) NOT NULL,
  T_LastName varchar(20) NOT NULL,
  T_Contact numeric(20) NOT NULL,
  
  CONSTRAINT PK_TEACHER PRIMARY KEY(T_ID)
  )
  
  CREATE TABLE TEA_COURSE
  (
   T_ID NUMERIC (10) NOT NULL,
   C_ID NUMERIC (10) NOT NULL,
  
   CONSTRAINT PK_TEA_COURSE PRIMARY KEY(T_ID,C_ID),
   CONSTRAINT FK_TEA_TEA_COURSE FOREIGN KEY(T_ID) REFERENCES Teacher(T_ID),
   CONSTRAINT FK_CLS_TEA_COURSE FOREIGN KEY(C_ID) REFERENCES Course(CRS_ID)
  )
  
  CREATE TABLE CLS_TEACHER
  (
    C_ID NUMERIC (10) NOT NULL,
    T_ID NUMERIC (10) NOT NULL,
  
    CONSTRAINT PK_CLS_TEACHER PRIMARY KEY(C_ID,T_ID),
    CONSTRAINT FK_Class_CLS_TEACHER FOREIGN KEY (C_ID) REFERENCES Class(C_ID),
    CONSTRAINT FK_TEACHER_CLS_TEACHER FOREIGN KEY (T_ID) REFERENCES Teacher(T_ID)
  )
  
  CREATE TABLE Education
  (
  E_ID NUMERIC(10) NOT NULL,
  E_Name VARCHAR(20) NOT NULL,
  E_Details VARCHAR(100)
  
  CONSTRAINT PK_EDUCATION PRIMARY KEY(E_ID)
  )
  
  CREATE TABLE TEA_EDU
  (
  T_ID NUMERIC(10) NOT NULL,
  E_ID NUMERIC(10) NOT NULL,
  
  CONSTRAINT PK_TEA_EDU PRIMARY KEY(T_ID,E_ID),
  CONSTRAINT FK_TEACHER_TEA_EDU FOREIGN KEY(T_ID) REFERENCES Teacher(T_ID),
  CONSTRAINT FK_EDUCATION_TEA_EDU FOREIGN KEY(E_ID) REFERENCES Education(E_ID)
  )

  Insertion: Do I have to insert data into junction tables manually in order to use join on the Teacher and Course

  -- Furqan Data insertion
  INSERT INTO Course (CRS_ID,C_ID,C_Title,C_Info) VALUES(1,1,'English',' abc xyz')
  INSERT INTO Course (CRS_ID,C_ID,C_Title,C_Info) VALUES(2,1,'Urdu',' abc xyz')
  INSERT INTO Course (CRS_ID,C_ID,C_Title,C_Info) VALUES(3,1,'Math',' abc xyz')
  INSERT INTO Course (CRS_ID,C_ID,C_Title,C_Info) VALUES(4,1,'',' abc xyz')
  --Education
  INSERT INTO Education (E_ID,E_Name,E_Details) VALUES(1,'MBA','abc xyz')
  INSERT INTO Education (E_ID,E_Name,E_Details) VALUES(2,'BSCS','abc xyz')
  INSERT INTO Education (E_ID,E_Name,E_Details) VALUES(3,'Bs Maths','abc xyz')
  INSERT INTO Education (E_ID,E_Name,E_Details) VALUES(4,'Bs Physics','abc xyz')
  
  --Teacher
  INSERT INTO Teacher (T_ID,T_FirstName,T_LastName,T_Contact) VALUES(1,'Ahmad','Shaikh',2)
  INSERT INTO Teacher (T_ID,T_FirstName,T_LastName,T_Contact) VALUES(2,'Naeem','Butt',3)
  INSERT INTO Teacher (T_ID,T_FirstName,T_LastName,T_Contact) VALUES(3,'Shagufta','Ejaz',1)

  select E.E_ID, E.E_Name,E.E_Details, T.T_ID,T.T_FirstName from Education E inner join TEA_EDU as te on e.E_ID = te.E_ID 
  inner join Teacher T on te.T_ID = T.T_ID   

  This query give null results.

  Thursday, June 27, 2013 1:05 PM