Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Create constraint on multiple columns

In this puzzle you have a table called Employees. In the table columns are – ID, EmployeeID, EmpName, IsValid. The requirement is that we should not have more than one IsValid = 1 per EmployeeID. What you do to handle this kind of situation while inserting data into the table ?

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE OneIsValid
(
	  ID BIGINT
	, EmployeeID BIGINT
	, EmpName VARCHAR(100)
	, IsValid INT
)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--

/* For this kind of requirement we should always create function like below. */


CREATE FUNCTION dbo.CheckIsValid
( 
	@EmployeeID INT 
)
RETURNS INT
AS 
BEGIN
		RETURN 
		(
			SELECT COUNT(*) cnt FROM dbo.OneIsValid  
			WHERE EmployeeID = @EmployeeID AND IsValid = 1
		)
END

ALTER TABLE OneIsValid
    ADD CONSTRAINT Ix_Constraint CHECK (dbo.CheckIsValid(EmployeeID) = 1)
GO

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements