Question

How to populate an SQL table that has two PK (foreign) that have different number of rows?

Example:

there are 3 tables. student, assigment, and hoursDedicated.

there are 5 students, and 3 assigments, and x hoursDedicated.

hoursDedicated has 2 Primary Keys, studentID and assigmentID. how do you populate hoursDedicated??

Thank you for your time.

Answer 1

This demonstration is using SQL Server.All this data is used for demonstration purpose only.

1.Table Name :student
create table student(
StudentId int primary key,
StudentName varchar(20));

/*inserting reconrds into student table*/
insert into student values (1,'S1');
insert into student values (2,'S2');

/*select student data*/
select * from student;'

Screen in SQL Server :

**********************************************

2.Table Name :assigment
create table assigment(
assignmentID int primary key,
assignmentName varchar(20));

/*inserting records into assignment table*/
insert into assigment values (1,'A1');
insert into assigment values (2,'A2');
insert into assigment values (3,'A3');

/*selecting data from assigment*/
select * from assigment;

Screen in SQL Server :

************************************************

3.Table Name :hoursDedicated
create table hoursDedicated(
StudentId int ,
assignmentID int,
hours decimal(6,2),
primary key(StudentId,assignmentID),
foreign key (StudentId) references Student(StudentId),
foreign key (assignmentID) references assigment(assignmentID));

/*inserting records into hoursDedicated table*/
insert into hoursDedicated values(1,1,5);
insert into hoursDedicated values (1,2,8);
insert into hoursDedicated values (1,3,5.2);
insert into hoursDedicated values (2,1,5.2);

/*selecting records*/
select * from hoursDedicated;

Screen in SQL Server :

********************************************