How to create a junction table and add index?

Question

Below is my code for creating my two tables, and since playlist and song are many-to-many relationships, I go ahead and create a junction table...

CREATE TABLE Playlist (
   Id VARCHAR(20),
   [Name] VARCHAR(50),
   Create_Date DATE,
   Username VARCHAR(20),
   PRIMARY KEY (Id),
   FOREIGN KEY (username) REFERENCES Users (username)
);  

CREATE TABLE Song (
   Id VARCHAR(20),
   Title VARCHAR(50),
   Genre VARCHAR(20),
   [Length] TIME,
   [Language] VARCHAR(20),
   Album_Id VARCHAR(20),
   Company_Id VARCHAR(20),
   PRIMARY KEY (Id),
   FOREIGN KEY (Company_Id) REFERENCES Company (Id),
   FOREIGN KEY (Album_Id) REFERENCES Album (Id)
);

CREATE TABLE Song_Playlist (
   Song_Id VARCHAR(20) NOT NULL,
   Playlist_Id VARCHAR(20) NOT NULL,
   PRIMARY KEY (Song_Id, Playlist_Id), 
   FOREIGN KEY (Song_id) REFERENCES Song (Id) ON DELETE RESTRICT ON UPDATE CASCADE,
   FOREIGN KEY (Playlist_Id) REFERENCES Playlist (Id) ON DELETE RESTRICT ON UPDATE CASCADE  
);

However, when I tried to create the junction table, it keeps getting me this error msg:

Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'RESTRICT'.

I am wondering how do I fix this problem, and also if I want to create an index for Genre in the Song table, how do I do it?

Answer 1

It seems like you are using SQL Server (according to the code and error message), but from docs online

Syntax:

| FOREIGN KEY   
    ( column [ ,...n ] )  
    REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
    [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
    [ NOT FOR REPLICATION ] 

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

this why you get an error Incorrect syntax near the keyword 'RESTRICT', cause SQL Server does not have ON DELETE RESTRICT