Not able to add a foreign key in already existing tables

Question

CREATE TABLE Artist
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    Forname varchar(50) NOT NULL
);

CREATE TABLE Song
(
    SongId INT PRIMARY KEY IDENTITY(1,1),
    Title varchar(50) NOT NULL,
    Length decimal(20, 6) NOT NULL,
    AlbumId int NOT NULL
);

My relations are many-to-many, so I have created an intermediary table. I'm trying to create some foreign key constraints for my table.

CREATE TABLE Artist_Song
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    ArtistId int NOT NULL,
    SongId int NOT NULL
);

ALTER TABLE Artist_Song
ADD CONSTRAINT fk_artistsong_artist
FOREIGN KEY (ArtistId)
REFERENCES Artist(Id);

ALTER TABLE Artist_Song
ADD CONSTRAINT fk_artistsong_song
FOREIGN KEY (SongId)
REFERENCES Song(Id);

But I keep receiving this error message:

Foreign key <fk_artistsong_song> references invalid column 'Id' in referenced table 'Song'

or

Foreign key <fk_artistsong_artist> references invalid column 'Id' in referenced table 'Artist'

I also tried creating a new column along with the restriction:

ALTER TABLE Song_Genre
    ADD SongIdTest INT
    CONSTRAINT FK_Test
        FOREIGN KEY (SongId) 
        REFERENCES Song(Id)

But I'm facing the same stuff.

What am I'm missing here?

Answer 1

In SQL Sever, your foreign key reference should be in the format of table_name(column_name). For song table, the id column provided in the description is named as songId instead of id. if that naming is indeed intended, the foreign key reference should be written as:

ALTER TABLE Artist_Song
ADD CONSTRAINT fk_artistsong_song
FOREIGN KEY (SongId)
REFERENCES Song(SongId);

Full DDL schema build after the above change are tested here:

CREATE TABLE Artist
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    FirstName varchar(50) NOT NULL,
    LastName varchar(50) NOT NULL,
    Forname varchar(50) NOT NULL
);

CREATE TABLE Song
(
    SongId INT PRIMARY KEY IDENTITY(1,1),
    Title varchar(50) NOT NULL,
    Length decimal(20, 6) NOT NULL,
    AlbumId int NOT NULL
);

CREATE TABLE Artist_Song
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    ArtistId int NOT NULL,
    SongId int NOT NULL
);

ALTER TABLE Artist_Song
ADD CONSTRAINT fk_artistsong_artist
FOREIGN KEY (ArtistId)
REFERENCES Artist(Id);

ALTER TABLE Artist_Song
ADD CONSTRAINT fk_artistsong_song
FOREIGN KEY (SongId)
REFERENCES Song(SongId);