CODEWITHSUNDEEP
sqlsql-serverdatabaseforeign-keysdata-integrity
Chad
Chad

Reputation: 432

Error Defining Foreign Key

I have the following two database tables defined:

CREATE TABLE [dbo].[Classrooms] (
  [ID]               INT          IDENTITY (1, 1) NOT NULL,
  [SystemAccount_ID] INT          NOT NULL,
  [ClassroomName]    VARCHAR (30) NOT NULL,
  CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ([ID]),
  CONSTRAINT [FK_Classrooms_SystemAccount] FOREIGN KEY ([SystemAccount_ID]) REFERENCES [dbo].[SystemAccounts] ([ID])
);

CREATE TABLE [dbo].[Students] (
   [ID]               INT          IDENTITY (1, 1) NOT NULL,
   [SystemAccount_ID] INT          NOT NULL,
   [Classroom_ID]     INT          NULL,
   [PhotoID]          INT          NULL,
   [FirstName]        VARCHAR (20) NOT NULL,
   [LastName]         VARCHAR (40) NOT NULL,
   [NewsTemplate]     TINYINT      NOT NULL,
   CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([ID] ASC),
   CONSTRAINT [FK_Students_Classrooms] FOREIGN KEY ([Classroom_ID]) REFERENCES [dbo].[Classrooms] ([ID]),
   CONSTRAINT [FK_Students_SystemAccounts] FOREIGN KEY ([SystemAccount_ID]) REFERENCES [dbo].[SystemAccounts] ([ID])
);

Data model details:

What I'm attempting to do is enforce when students are added to a classroom (by setting the Classroom_ID key in the Students table) that the classroom belongs to the same system account as the student. I could easily enforce this at the business logic layer but then I'd be requiring every programmer to remember to do this. So ideally, I'd be able to do this at the data layer as a constraint.

I tried adding a FK constraint to the Students table:

CONSTRAINT [FK_Students_ToTable] FOREIGN KEY ([SystemAccount_ID]) REFERENCES [Classrooms]([SystemAccount_ID])

Which results in the following error compliments of SQL Server:

Update cannot proceed due to validation errors.
Please correct the following errors and try again.

SQL71516 :: The referenced table '[dbo].[Classrooms]' contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column is a computed column, it should be persisted.

I've tried a few different things but my SQL mojo isn't powerful enough to hack past this one. Any help would be greatly appreciated.

Upvotes: 1

Views: 945

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Add a UNIQUE constraint on the combination of the two columns in Classrooms:

CREATE TABLE [dbo].[Classrooms] (
  [ID]               INT          IDENTITY (1, 1) NOT NULL,
  [SystemAccount_ID] INT          NOT NULL,
  [ClassroomName]    VARCHAR (30) NOT NULL,
  CONSTRAINT [PK_Table] 
    PRIMARY KEY CLUSTERED ([ID]),
  CONSTRAINT [FK_Classrooms_SystemAccount] 
    FOREIGN KEY ([SystemAccount_ID]) 
    REFERENCES [dbo].[SystemAccounts] ([ID]),
  CONSTRAINT [UQ_Classrooms_ID_SystemAccount_ID] 
    UNIQUE ([SystemAccount_ID], [ID])
);

Then, in the Students table, combine the two FOREIGN KEY constraints into one, or in your case (because Classroom_ID isnullable), change the FK to Classroom to use the combination of the two columns:

CREATE TABLE [dbo].[Students] (
  [ID]               INT          IDENTITY (1, 1) NOT NULL,
  [SystemAccount_ID] INT          NOT NULL,
  [Classroom_ID]     INT          NULL,
  [PhotoID]          INT          NULL,
  [FirstName]        VARCHAR (20) NOT NULL,
  [LastName]         VARCHAR (40) NOT NULL,
  [NewsTemplate]     TINYINT      NOT NULL,
  CONSTRAINT [PK_Students] 
    PRIMARY KEY CLUSTERED ([ID] ASC),
  CONSTRAINT [FK_Students_Classrooms] 
    FOREIGN KEY ([SystemAccount_ID], [Classroom_ID]) 
    REFERENCES [dbo].[Classrooms] ([SystemAccount_ID], [ID]),
  CONSTRAINT [FK_Students_SystemAccounts]         -- this wouldn't be needed if
    FOREIGN KEY ([SystemAccount_ID])              -- Classrooms_ID was NOT NULL
    REFERENCES [dbo].[SystemAccounts] ([ID])
);

Upvotes: 1

Related Questions