Constraint Across 2 Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Constraint Across 2 Tables

Author

Topic

yoyolame
Starting Member

8 Posts

Posted - 2009-01-29 : 02:32:28

Hi all, currently I am facing a problem.
Its a constraint across 2 tables.

Students (StudentId, StudentName)
PK: StudentId

Modules (ModulesId, ModuleName, ModuleDesc)
PK: ModuleId

StudentsModules (StudModId, StudentId, ModuleId)
PK: StudModId
FK: StudentId REF Students
FK: ModuleId REF Modules

Constraint:
Each student can have zero modules (Because the student has just registered for this School)
Student cannot have only 1 module. In other words, once a student registers for a module, he/she need to have at least 2 modules.

Summary:Student can only have 0, 2, 3, etc modules but not 1.
How can I enable such constraint in StudentsModules table?

Thank you in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 02:41:51

It will be nearly impossible for you to make inserts in that table.
You will be forces to insert TWO records in same insert statement to bypass the contraint.

The constraint enformcement is easily built with a trigger.

E 12°55'05.63"
N 56°04'39.26"

yoyolame
Starting Member

8 Posts

Posted - 2009-01-29 : 02:44:20

quote:
Originally posted by Peso

It will be nearly impossible for you to make inserts in that table.
You will be forces to insert TWO records in same insert statement to bypass the contraint.

The constraint enformcement is easily built with a trigger.

E 12°55'05.63"
N 56°04'39.26"

Hi Peso. Glad to see u again.
That is the problem I face. Because MS-SQL only let u insert 1 row at a time. Can u show me how to code using Trigger?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 02:46:24

No, you can insert two records at the same time

INSERT StudentsModules (StudentId, ModuleId)
select 1, 1 union all
select 1, 2

E 12°55'05.63"
N 56°04'39.26"

yoyolame
Starting Member

8 Posts

Posted - 2009-01-29 : 02:50:19

quote:
Originally posted by Peso

No, you can insert two records at the same time

INSERT StudentsModules (StudentId, ModuleId)
select 1, 1 union all
select 1, 2

E 12°55'05.63"
N 56°04'39.26"

sorry. but I don't understand
"select 1, 1 union all
select 1, 2"

yoyolame
Starting Member

8 Posts

Posted - 2009-01-29 : 02:53:00

quote:
Originally posted by yoyolame

quote:
Originally posted by Peso

No, you can insert two records at the same time

INSERT StudentsModules (StudentId, ModuleId)
select 1, 1 union all
select 1, 2

E 12°55'05.63"
N 56°04'39.26"

sorry. but I don't understand
"select 1, 1 union all
select 1, 2"

Sorry. Think I got it. It using records from other tables to insert into the table.

But can u show me how to implement the constraint using trigger?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 03:06:41

[code]CREATE TRIGGER dbo.trgStudentModules_Insert_Delete_Update
ON dbo.StudentModules
AFTER INSERT,
DELETE,
UPDATE
AS

IF EXISTS (
SELECT sm.StudentID
FROM (
SELECT StudentID
FROM inserted

UNION

SELECT StudentID
FROM deleted
) AS t
INNER JOIN dbo.StudentModules AS sm ON sm.StudentID = t.StudentID
GROUP BY sm.StudentID
HAVING COUNT(DISTINCT sm.ModuleID) = 1
)
BEGIN
RAISERROR('Student found with only one module.', 16, 1)
ROLLBACK TRAN
END
GO[/code]

E 12°55'05.63"
N 56°04'39.26"

yoyolame
Starting Member

8 Posts

Posted - 2009-01-29 : 22:16:25

quote:

Originally posted by Peso

CREATE TRIGGER	dbo.trgStudentModules_Insert_Delete_Update
ON		dbo.StudentModules
AFTER		INSERT,
		DELETE,
		UPDATE
AS

IF EXISTS	(
			SELECT		sm.StudentID
			FROM		(
						SELECT	StudentID
						FROM	inserted

						UNION

						SELECT	StudentID
						FROM	deleted
					) AS t
			INNER JOIN	dbo.StudentModules AS sm ON sm.StudentID = t.StudentID
			GROUP BY	sm.StudentID
			HAVING		COUNT(DISTINCT sm.ModuleID) = 1
		)
	BEGIN
		RAISERROR('Student found with only one module.', 16, 1)
		ROLLBACK TRAN
	END
GO

E 12°55'05.63"
N 56°04'39.26"

wow! its not easy. no wonder I can't get it right!
thx again peso!
great help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 01:12:18

I guess the trigger is working then?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources