Karl_mark
asked on
Enforcing Data Integrity Across Three Columns in SQL
I have a bit of a question regarding data integrity on a table I'm creating. For reference, here is the code to create the table:
Which is all fine and fairly straight forward. Each module can only be associated with each course once for each academic year.
Then I start to struggle. For each course there is a module which is called the "Cash-In Code" Each course can only have one cash-in code for each Student Level. There can be multiple modules which are not cash-in codes for each course within an academic year and student level. So, I can't place a unique index on the CashInCode, CourseCode, AcademicYear, StudentLevel columns the CashInCode column can have the value 0 more than once.
I hope that makes sense!
I looked at adding a WITH CHECK Constraint, but I don't think they can be applied the way I want (or can they?). Is there a solution to this without using a trigger?
CREATE TABLE tbl_Course_ExamModules
(
CourseExamModuleID INT IDENTITY(1,1) NOT NULL,
CourseCode VARCHAR(3) NOT NULL,
ExamModule VARCHAR(10) NOT NULL,
CashInCode BIT NULL,
AcademicYear INT NOT NULL,
StudentLevel INT NOT NULL,
CourseModuleCount INT NOT NULL
)
ALTER TABLE tbl_Course_ExamModules
ADD CONSTRAINT pk_Course_ExamModules_CourseExamModuleID
PRIMARY KEY CLUSTERED (CourseExamModuleID)
CREATE UNIQUE NONCLUSTERED INDEX idx_CourseExamModules_Code_Module_Year
ON tbl_Course_ExamModules (CourseCode, ExamModule, AcademicYear)
Which is all fine and fairly straight forward. Each module can only be associated with each course once for each academic year.
Then I start to struggle. For each course there is a module which is called the "Cash-In Code" Each course can only have one cash-in code for each Student Level. There can be multiple modules which are not cash-in codes for each course within an academic year and student level. So, I can't place a unique index on the CashInCode, CourseCode, AcademicYear, StudentLevel columns the CashInCode column can have the value 0 more than once.
I hope that makes sense!
I looked at adding a WITH CHECK Constraint, but I don't think they can be applied the way I want (or can they?). Is there a solution to this without using a trigger?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Barry. Very close to the solution I came up with. I hadn't thought of using a function to do the job!
It is possible to add a constraint which references a function - e.g.
CONSTRAINT chkCheckAge CHECK(dbo.fnGetAge(DateOfB
so the use of a function in a CHECK constraint may open up some possibilities for your scenario