quote:

---

Originally posted by LC
...Is there a way to enforce a primary key on the parent table where the dates can't overlap for the same Code?

Second question, is there a way to declare a FK so that any date in the range will be valid?

Thanks...

---

quote:

---

Originally posted by ehorn

quote:

---

Originally posted by LC
...Is there a way to enforce a primary key on the parent table where the dates can't overlap for the same Code?

Second question, is there a way to declare a FK so that any date in the range will be valid?

Thanks...

---

One option is to construct scalar UDF's to implement your checks and then assign them as CHECK CONSTRAINT's on your table(s).

HTH.

---

--*************************************************************************
-- Parent Table CHECK CONSTRAINTS
--*************************************************************************

--udf to perform the check
CREATE FUNCTION dbo.udfIsValidDateRange
(
	@StartDate	DATE,
	@EndDate	DATE,
	@Code		INT
)
RETURNS BIT
AS
BEGIN

	DECLARE @isValid BIT
	SET @isValid = 1
	
	IF EXISTS
	(	
		SELECT 1
		FROM dbo.parent
		WHERE Code = @Code 
			AND (	@StartDate != StartDate OR @EndDate != EndDate)
			AND (
					(@StartDate > StartDate AND @StartDate < EndDate)
					OR
					(@EndDate > StartDate AND @EndDate < EndDate) 
				)
	) 
	SET @isValid = 0
	RETURN @isValid
END
GO

--table with constraints applied
CREATE TABLE dbo.parent 
(
	StartDate	DATE,
	EndDate		DATE,
	Code		INT,
	Value		VARCHAR(255),
	PRIMARY KEY(StartDate, EndDate, Code),
	CHECK ( StartDate < EndDate ),
	CONSTRAINT ck_IsValidDateRange CHECK (dbo.udfIsValidDateRange(StartDate, EndDate, Code) = 1)
)
GO

--pass of Code=1
INSERT INTO dbo.parent VALUES('2011/01/01','2011/06/30',1,'The value from Jan-Jun') --wors fine
INSERT INTO dbo.parent VALUES('2011/07/01','2011/12/31',1,'The value from Jul-Dec') --works fine

--fail for Code=1
INSERT INTO dbo.parent VALUES('2011/05/01','2011/06/30',1,'The value from May-Jun') --fails overlap in startdate and enddate

--pass for Code=2
INSERT INTO dbo.parent VALUES('2011/03/01','2011/10/30',2,'The value from Mar-Oct') --work fine
INSERT INTO dbo.parent VALUES('2011/01/01','2011/03/01',2,'The value from Jan-Mar') --work fine

--fail for Code=2
INSERT INTO dbo.parent VALUES('2011/07/01','2011/12/31',2,'The value from Jul-Dec') --fails overlap in startdate
INSERT INTO dbo.parent VALUES('2011/02/01','2011/04/20',2,'The value from Feb-Apr') --fails overlap in enddate

SELECT * FROM parent

--*************************************************************************
-- Child Table CHECK CONSTRAINTS
--*************************************************************************

--udf to perform the date check
CREATE FUNCTION dbo.udfIsValidDate
(
	@TheDate	DATE,
	@Code		INT
)
RETURNS BIT
AS
BEGIN

	DECLARE @isValid BIT
	SET @isValid = 0
	
	IF EXISTS
	(	
		SELECT 1
		FROM dbo.parent
		WHERE Code = @Code 
			AND @TheDate >= StartDate
			AND @TheDate <= EndDate
	) 
	SET @isValid = 1
	RETURN @isValid
END
GO

--udf to perform the Code check
CREATE FUNCTION dbo.udfIsValidCode
(
	@Code		INT
)
RETURNS BIT
AS
BEGIN

	DECLARE @isValid BIT
	SET @isValid = 0
	
	IF EXISTS
	(	
		SELECT 1
		FROM dbo.parent
		WHERE Code = @Code 
	) 
	SET @isValid = 1
	RETURN @isValid
END
GO

CREATE TABLE dbo.child 
(
	TheKey		INT PRIMARY KEY,
	TheDate		DATE,
	Code		INT,
	CONSTRAINT ck_Code CHECK (dbo.udfIsValidCode(Code) = 1),
	CONSTRAINT ck_IsValidDate CHECK (dbo.udfIsValidDate(TheDate, Code) = 1)
)

--passes for Code=1
INSERT INTO child values(1,'2011/09/23',1)
INSERT INTO child values(2,'2011/11/05',1)

--fails for Code=1
INSERT INTO child values(3,'2010/09/23',1)

--passes for Code=2
INSERT INTO child values(4,'2011/09/23',2)

--fails for Code=2
INSERT INTO child values(5,'2010/12/31',2)

--fails for Code=3 (not found in parent)
INSERT INTO child values(5,'2010/12/31',3)

SELECT * FROM child

--DROP TABLE dbo.parent, dbo.child
--DROP FUNCTION dbo.udfIsValidDateRange,  dbo.udfIsValidDate

quote:

---

Originally posted by ehorn

If this method was emplyed to check date ranges, you could consider changing your composite key selection on parent table to say; [Code] only. This will allow you to have a relationship with child on [Code]. and then not require the additional check constraint on child(ck_Code). Just some thoughts for your consideration.

Hope that helps stimulate some thought/options for you.

Have a nice evening.

---