CODEWITHSUNDEEP
sqlsql-server
Joel
Joel

Reputation: 6213

Update value in another table based on ID reference

I have two tables called Jobs & Tags. In the Jobs table I have a column called TagID. I want this to be an ID that references TagID in the Tags table.

Jobs

|Id, CompanyName, Title, Link, TagId, PublishedDate, Country, City, Description|

Tags 

|Id, TagId, Tag|

Basically I want Jobs.TagId to reference Tags.TagId and when an entry in Jobs is modified I want an entry in Tags to respect that change (i.e. if an entry in Jobs is deleted, all entries with that jobs TagId in Tags should also be deleted).

What I've tried:

CREATE TABLE Tags(
    Id int NOT NULL PRIMARY KEY, 
    TagID int NOT NULL,
    Tag varchar(255) NOT NULL 
)

CREATE TABLE Jobs (
    Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    CompanyName varchar(255), 
    Title varchar(255), 
    Link varchar(255), 
    TagID int UNIQUE NOT NULL CONSTRAINT FK_TagID FOREIGN KEY (Tags) REFERENCES Tags(TagID) ON DELETE CASCADE ON UPDATE CASCADE,
    PublishedDate DateTime,
    Country varchar(255),
    City varchar(255),
    Description varchar(MAX)
)

Example structure I want to achieve:

Jobs

|Id, CompanyName, Title,           Link, TagId, PublishedDate, Country, City,     Description|
|1,  Apple,       Senior Engineer, link, 12,    2019-12-13,    USA,     Michigan, Fun Job    |

Tags

|Id, TagId, Tag        |
|1,  12,     C++       |
|2,  12,     C#        |
|3,  12,     Javascript|

Now, if Jobs.Id 1 is deleted, all the entries in Tags with the TagId 12 should be deleted. How can I achieve this behaviour?

Right now I get:

There are no primary or candidate keys in the referenced table 'Tags' that match the referencing column list in the foreign key 'FK_TagID'.

Upvotes: 0

Views: 859

Answers (2)

Joel
Joel

Reputation: 6213

In order to get the desired behaviour, all I really had to do was to move the query to the other table.

CREATE TABLE Jobs (
    Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    CompanyName varchar(255), 
    Title varchar(255), 
    Link varchar(255), 
    TagID int UNIQUE NOT NULL,
    PublishedDate DateTime,
    Country varchar(255),
    City varchar(255),
    Description varchar(MAX)
)

CREATE TABLE Tags(
    Id int NOT NULL PRIMARY KEY, 
    TagID int,
    Tag varchar(255) NOT NULL,
    CONSTRAINT FK_TagID FOREIGN KEY (TagID)
    REFERENCES Jobs(TagID) ON DELETE CASCADE ON UPDATE CASCADE
)

Now If I have these entries:

Jobs

|Id, CompanyName, Title,           Link, TagId, PublishedDate, Country, City,     Description|
|1,  Apple,       Senior Engineer, link, 12,    2019-12-13,    USA,     Michigan, Fun Job    |

Tags

|Id, TagId, Tag        |
|1,  12,     C++       |
|2,  12,     C#        |
|3,  12,     Javascript|

If I delete Jobs.Id = 1 all the entries in Tags.TagID = 12 will be deleted.

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.

From Books Online:

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

CREATE TABLE Tags(
    Id int NOT NULL PRIMARY KEY, 
    TagID int UNIQUE NOT NULL,
    Tag varchar(255) NOT NULL 
)

CREATE TABLE Jobs (
    Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    CompanyName varchar(255), 
    Title varchar(255), 
    Link varchar(255), 
    TagID int UNIQUE NOT NULL CONSTRAINT FK_TagID FOREIGN KEY REFERENCES Tags(TagID) ON DELETE CASCADE ON UPDATE CASCADE,
    PublishedDate DateTime,
    Country varchar(255),
    City varchar(255),
    Description varchar(MAX)
)

Upvotes: 1

Related Questions