Can I do two select on a database with two primary keys?

Question

Hello all Im working on the db for a music app and I have this so far.

Song 
SongId Name Artist

Playlist
PlaylistId Name

Playlist_with_songs
id (fk)PlaylistId  (fk)SongId

last table both are a primary key.

So I am wondering how my query will look like. Ex. If I want to get all the songs with playlistId 5, then I would have to get the actual songs from the Song tables what statement could I use.

Also, Is this a good format to follow if in the case of a playlist being deleted BUT still keeping all the songs available. Would cascade work there?

Thanks (I am using sqlite.)

If you want to get all the songs with playlistId 5 then you can use raw query like Select * from Song where SongId IN ( select SongId from Playlist_with_songs where PlaylistId = 5); — Sandeep Bhandari, Apr 07 '16 at 07:33
As far as your second question "Is this a good format to follow if in the case of a playlist being deleted BUT still keeping all the songs available" is concerned I think its good enough :) Foreign key constraints are not enabled by default in Sqlite you should enable it using db.execSQL("PRAGMA foreign_keys=ON;"); But for your case I believe its good if you dont enable it :) and then define cascade rules :) Should work fine :) I hope I made a little sense :) Anyway refer http://stackoverflow.com/questions/2545558/foreign-key-constraints-in-android-using-sqlite-on-delete-cascade — Sandeep Bhandari, Apr 07 '16 at 07:43

Pavel Gatnar · Accepted Answer · 2016-04-07T07:43:40.183

1

SQL part

The old-fashioned way:

SELECT * FROM Song
WHERE SongId IN (
  SELECT SongId FROM Playlist_with_songs
  WHERE Playlist_id = 5)

The modern way:

SELECT s.* FROM Song s
JOIN Playlist_with_songs ps ON ps.SongId = s.SongId
  AND ps.Playlist_id = 5

Delete part

Exactly this pattern is used for n:m relationships. You should define ON DELETE CASCADE for both FKs.

edited Apr 07 '16 at 07:43

answered Apr 07 '16 at 07:35

Pavel Gatnar

3,987
2
19
29

hey do you have any links for the modern way. The first one worked for me but the second it didn't But I am intrigued of why this is the modern way. Thanks – Beto Apr 08 '16 at 03:27
@Beto maybe you need to list the columns instead of s.* (some dbs do not support this) – Pavel Gatnar Apr 08 '16 at 06:20

score 0 · Answer 2 · answered Apr 07 '16 at 07:38

0

If you would like to get all songs included in a playlist, you may use a join clause, i.e:

SELECT Song.SongId, Song.Name, Song.Artist FROM Playlist_with_songs
LEFT JOIN Song
ON Song.SongId = Playlist_with_songs.SongId
WHERE Playlist_with_songs.PlaylistId = 5;

answered Apr 07 '16 at 07:38

Dominick Navarro

752
6
20

@PavelGatnar So that you get a single row with NULLs when the playlist is empty. Assuming that is the result that's wanted … – CL. Apr 07 '16 at 08:22

Can I do two select on a database with two primary keys?

2 Answers2