I have a simple database structure:

CREATE TABLE dbo.Report
(
    ID int NOT NULL,
    Name varchar(50) NOT NULL
)

CREATE TABLE dbo.ReportText
(
    ID int NOT NULL,
    Content varchar(max) NOT NULL,
    FK_ReportID int NOT NULL,
    FK_FontID int NOT NULL
) 

CREATE TABLE dbo.Font
(
    ID int NOT NULL,
    Name varchar(100) NOT NULL,
    FK_ReportID int NOT NULL
) 

In plain English:

• A Report contains multiple ReportText rows
• Each ReportText has a Font
• Each Font is restricted to a Report
  • i.e. The ReportTexts for ReportA cannot use any of the Fonts for ReportB

I can enforce everything with simple foreign keys, except that last requirement. I can have:

• a foreign key from Report.ID to ReportText.FK_ReportID
• a foreign key from Report.ID to Font.FK_ReportID

...but I need a third relationship that will prevent a ReportText from selecting a Font for a report ID different from its own FK_ReportID.

Is this possible or is there a problem with my schema?

CodePudding user response：

The Check Constraint with aid from a function should be something like this

CREATE FUNCTION dbo.CheckFontUsage (@ReportID int, @FontID int)
RETURNS bit
AS
BEGIN
    DECLARE @AlreadyUsed bit;
    SELECT @AlreadyUsed = IIF(COUNT(*) > 0,1,0) FROM dbo.ReportText WHERE FK_ReportID <> @ReportID AND FK_FontID = @FontID

    RETURN(@AlreadyUsed);
END

GO

ALTER TABLE dbo.ReportText 
ADD CONSTRAINT CK_YourConstrName CHECK (dbo.CheckFontUsage(FK_ReportID,FK_FontID) = 0)
GO

Choose meaningful names for both the constraint and function

CodePudding user response：

I think we can do this without the need for a function:

/* For testing we may want to drop these
DROP TABLE IF EXISTS ReportText;
DROP TABLE IF EXISTS FontsReports
DROP TABLE IF EXISTS Reports;
DROP TABLE IF EXISTS Fonts;
*/

CREATE TABLE Reports (ID INT IDENTITY PRIMARY KEY, Name VARCHAR(50) NOT NULL);
CREATE TABLE Fonts (ID INT PRIMARY KEY IDENTITY, Name NVARCHAR(100))

CREATE TABLE FontsReports (FontID INT NOT NULL FOREIGN KEY REFERENCES Fonts(ID), 
                           ReportID INT NOT NULL FOREIGN KEY REFERENCES Reports(ID));
CREATE TABLE ReportText (ID INT IDENTITY, Content NVARCHAR(MAX) NOT NULL, ReportID INT FOREIGN KEY REFERENCES Reports(ID), 
                                                                          FontID INT FOREIGN KEY REFERENCES Fonts(ID));
INSERT INTO Reports (Name) VALUES 
('Allow Font one'),('Allow Font two'),('Allow font one and two');

INSERT INTO Fonts (NAME) VALUES 
('Font one'),('Font two'),('Font three');

INSERT INTO FontsReports (FontID, ReportID) VALUES
(1,1),(1,3),(2,2),(2,3);

ALTER TABLE FontsReports ADD PRIMARY KEY(FontID, ReportID)

ALTER TABLE FontsReports WITH CHECK ADD CONSTRAINT AllowedFontAndReport 
FOREIGN KEY(FontID)   REFERENCES Fonts(ID), 
FOREIGN KEY(ReportID) REFERENCES Reports(ID)

ALTER TABLE ReportText WITH CHECK ADD CONSTRAINT AllowedFontReport 
FOREIGN KEY(FontID, ReportID) REFERENCES FontsReports (FontID, ReportID)

INSERT INTO ReportText (Content, ReportID, FontID) VALUES 
('Something that works.', 1, 1)

INSERT INTO ReportText (Content, ReportID, FontID) VALUES 
('Something that fails', 2, 1)

This should create the tables and add constraints to them. It does utilize a lookup to force the composite key and make it referenceable.

I'm not sure it's a better answer than the one previously posted, but it was neat to write.