CODEWITHSUNDEEP
sqlsql-servert-sqlforeign-keys
MSOACC
MSOACC

Reputation: 3685

SQL relationship to enforce rule that a table can only reference rows of another table with the same foreign key

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:

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

...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?

Upvotes: 0

Views: 64

Answers (3)

Dan Guzman
Dan Guzman

Reputation: 46425

You can use declarative constraints with your existing model to limit a font to a single report and only that report. This can be accomplished by adding a composite key unique constraint on the Font table ID and FK_ReportID columns and reference the alternate key with a foreign key constraint on the ReportText table.

Example DDL:

CREATE TABLE dbo.Report
(
    ID int NOT NULL CONSTRAINT PK_Report PRIMARY KEY,
    Name varchar(50) NOT NULL
);

CREATE TABLE dbo.Font
(
    ID int NOT NULL CONSTRAINT PK_Font PRIMARY KEY,
    Name varchar(100) NOT NULL,
    FK_ReportID int NOT NULL
        CONSTRAINT FK_Font_Report FOREIGN KEY REFERENCES dbo.Report(ID),
    CONSTRAINT AK_Font_ID_FK_ReportID UNIQUE(ID, FK_ReportID)
);

CREATE TABLE dbo.ReportText
(
    ID int NOT NULL CONSTRAINT PK_ReportText PRIMARY KEY,
    Content varchar(max) NOT NULL,
    FK_ReportID int NOT NULL CONSTRAINT FK_ReportText_Report FOREIGN KEY REFERENCES dbo.Report(ID),
    FK_FontID int NOT NULL,
    CONSTRAINT FK_ReportText_Font FOREIGN KEY (FK_FontID, FK_ReportID) REFERENCES dbo.Font(ID, FK_ReportID),
);

INSERT INTO dbo.Report VALUES(1,'Report1');
INSERT INTO dbo.Report VALUES(2,'Report2');

INSERT INTO dbo.Font VALUES(1,'Font1',1);
INSERT INTO dbo.Font VALUES(2,'Font2',1);
INSERT INTO dbo.Font VALUES(3,'Font3',2);

INSERT INTO dbo.ReportText VALUES(1,'content1',1,1);
INSERT INTO dbo.ReportText VALUES(2,'content2',1,1);
INSERT INTO dbo.ReportText VALUES(3,'content3',1,2);
INSERT INTO dbo.ReportText VALUES(4,'content1',2,3);

--this fails because font 3 is not valid for report 1
INSERT INTO dbo.ReportText VALUES(5,'content4',1,3);
--this fails because font 1 is not valid for report 2
INSERT INTO dbo.ReportText VALUES(5,'content2',2,1);

Upvotes: 1

Patrick Hurst
Patrick Hurst

Reputation: 2853

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.

Upvotes: 1

Giovanni Luisotto
Giovanni Luisotto

Reputation: 1400

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

Upvotes: 2

Related Questions