CODEWITHSUNDEEP
sql-server
The Dark Prince
The Dark Prince

Reputation: 55

Cannot get the foreign key constraint from another table to display in SQL-Server

I have 3 tables and the schema setup is like this:

tenant object: tenant id, first name, last name, phone, email, apartment id

apartment object: apartment id, address, square footage, monthly utility fee, monthly parking fee, last cleaning date

contract object: contract id, apartment id, tenant id, start date, end date, monthly rent

Here is my script:

CREATE TABLE [dbo].[Contract]
(
    [ContractId] INT IDENTITY (1, 1) NOT NULL,
    [StartDate]        DATETIME NULL,
    [EndDate]     DATETIME NULL,
    [MonthlyRent]    FLOAT NULL,
    [AptId] INT NOT NULL,
    [TenantId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([ContractId] ASC),
    CONSTRAINT [FK_dbo.Contract_dbo.Apartment_AptId] FOREIGN KEY ([AptId]) 
        REFERENCES [dbo].[Apartment] ([AptId]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.Contract_dbo.Tenant_TenantId] FOREIGN KEY ([TenantId]) 
        REFERENCES [dbo].[Tenant] ([TenantId]) ON DELETE CASCADE
)

It complains when I am trying to add the foreign key constraint for the Apartment table. I know why it is because the Tenant Table already holds the Apartment foreign key constraint when I am trying to include it into the Contract table. But I still need to include the AptId column in the Contract table and populate it with the correct Ids. Is there a way to do that without having to do an inner join of sort?

Upvotes: 0

Views: 75

Answers (1)

Eli
Eli

Reputation: 2608

The issue which you seem to be running into is a cyclical or circular reference.

On delete cascade will delete all references to the given record as a foreign key. Case in point, if you delete a record in the apartment table, all tenant records referencing that apartment will be deleted. The issue which you seem to have is that you will have two correct "paths" or ways in which a contract record can be deleted, and a third path which is causing the trouble:

  1. A record in the apartment table gets deleted
  2. A record in the tenant table gets deleted.
  3. A record in the apartment table gets deleted, which in turn will delete the contracts for the given tenant (this is the troublesome piece).

Your question asks for a way to keep the FK in place (in terms of not requiring a join), without running into this issue. You can do that with the following modification to your create table script:

I commented out your apartment_id field and replaced it with an identical one albeit without the "on delete cascade" clause. Since that was the issue, you can still keep the FK without running into a circular reference. 

CREATE TABLE [dbo].[Contract]
(
    [ContractId] INT IDENTITY (1, 1) NOT NULL,
    [StartDate]        DATETIME NULL,
    [EndDate]     DATETIME NULL,
    [MonthlyRent]    FLOAT NULL,
    [AptId] INT NOT NULL,
    [TenantId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([ContractId] ASC),
    /*CONSTRAINT [FK_dbo.Contract_dbo.Apartment_AptId] FOREIGN KEY ([AptId]) 
        REFERENCES [dbo].[Apartment] ([AptId]) ON DELETE CASCADE,*/
    CONSTRAINT [FK_dbo.Contract_dbo.Apartment_AptId] FOREIGN KEY ([AptId]) 
            REFERENCES [dbo].[Apartment] ([AptId]),
    CONSTRAINT [FK_dbo.Contract_dbo.Tenant_TenantId] FOREIGN KEY ([TenantId]) 
        REFERENCES [dbo].[Tenant] ([TenantId]) ON DELETE CASCADE
)

Give it a run and it should work just fine for you.

Upvotes: 1

Related Questions