How do I correctly design a many-to-many relationship in SQL Server?

Posted by Prabu_Spark under Sql Server on 10/30/2014 | Points: 10 | Views : 489 | Status : [Member] | Replies : 1
Hi sir,

How to implement a many-to-many relationship in SQL Server via the designer? Kindly give me the solution for this problem.


[Note: How to design a tables that implement many to many relationship in sql server]

With regards,
J.Prabu.
[Email:prbspark@gmail.com]



Responses

Posted by: Bandi on: 10/31/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--Sample script script to implement Many-to-Many relationships in database

CREATE TABLE Students(
StudentID int Primary Key,
FirstName nvarchar(30),
LastName nvarchar(50),
FullName AS (FirstName + ' ' + LastName)
)
GO

CREATE TABLE Classes(
ClassID int Primary Key,
ClassTitle varchar(50)
)
GO

CREATE TABLE ClassGrades(
ClassID int,
StudentID int,
GradeLetter varchar(2),
Constraint PK_ClassGrades
PRIMARY KEY(ClassID, StudentID),
Constraint FK_Classes_ClassID
FOREIGN KEY(ClassID)
REFERENCES Classes(ClassID) ON UPDATE CASCADE,
Constraint FK_Students_StudentID
FOREIGN KEY(StudentID)
REFERENCES Students(StudentID) ON UPDATE CASCADE
)
GO

--Insert classes rows
INSERT Classes VALUES(1,'SQL')
INSERT Classes VALUES(999,'Java')
GO


--Insert Students rows
INSERT Students VALUES(1, 'Poor', 'DBA')
INSERT Students VALUES(2, 'Better', 'DBA')
GO

--Insert ClassGrades rows
INSERT ClassGrades VALUES(1, 1, 'C+')
INSERT ClassGrades VALUES(1, 2, 'A+')
INSERT ClassGrades VALUES(999, 2, 'A')
GO

--Show table values after initial population
SELECT S.FullName, C.ClassTitle, CG.GradeLetter
FROM Classes C, ClassGrades CG, Students S
WHERE C.ClassID = CG.ClassID AND
S.StudentID = CG.StudentID
GO
FullName ClassTitle GradeLetter
--------------------------------------------------------------------------------- -------------------------------------------------- -----------
Poor DBA SQL C+
Better DBA SQL A+
Better DBA Java A

--cleanup script
drop table ClassGrades;
drop table Classes;
drop table Students;
GO


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response