Faced the following problem: I have the following tables Table with songs

and

Table with lyrics

To implement deletion by limiting used:

ALTER TABLE dbo.Songs ADD CONSTRAINT FK_Songs_Texts FOREIGN KEY (Text_ID) REFERENCES dbo.Texts(ID) ON DELETE CASCADE

It is worth considering that when adding a song to the database, NULL is put in the Text_ID field. Text can be added only when the song information changes. Song_Title stores the song ID . When I delete a song, the song record from the Songs table is deleted, and the text from the Texts table is not deleted. Maybe we should change the limit from Songs.ID to Texts.Song_Title ? Or you can still do something with the original version?

  • In my opinion, you do the FK in the wrong direction. You will delete the song when you delete the text. As I understand it, you want the opposite. Then you need a foreign key in the Texts table referring to the Songs table - Viktorov
  • one
    I would say that, obviously, you need to change to songs.id -> texts.song_title simultaneously renaming the latter song_id - teran
  • @teran yes, that's what I meant. Now I will try to throw through song_id - SNuSNuMR1996

1 answer 1

On the advice of @teran, I made a restriction on Songs.ID -> Texts.Song_ID and it all worked!

PS query changing the EXEC sp_RENAME 'dbo.Texts.Song_Title', 'Song_ID', 'COLUMN' column name EXEC sp_RENAME 'dbo.Texts.Song_Title', 'Song_ID', 'COLUMN'