Unique key based on many-to-many pivot table
The simplest and probably most common way would be to declare the (album_id, artist_id) pair of columns a unique composite key.

There are two variations of this approach. First, you can keep the current structure of the album_artist table and simply add a unique constraint on the said two columns:

ALTER TABLE album_artist
ADD CONSTRAINT uq_album_artist
  UNIQUE (album_id, artist_id);


There are more options to adding a UNIQUE constraint, which can be found in the linked manual.

The second variation is to get rid of the id column and declare (album_id, artist_id) to be the primary key of the table, as Rick James has suggested in a comment:

ALTER TABLE album_artist
DROP PRIMARY KEY;  /* assuming id has actually been declared
                      as the PK; if not, omit this step */

ALTER TABLE album_artist
DROP id;

ALTER TABLE album_artist
ADD CONSTRAINT pk_album_artist
  PRIMARY KEY (album_id, artist_id);


A junction table like your album_artist table, which stores no additional information beyond the table references, usually does not need to be referenced by other tables – at least, not often enough to justify the need for a dedicated ID column. The second variation, therefore, may be more appropriate. Rick's other useful thoughts on implementation of a junction table can be found in his blog.

If another table needs to reference an album and an artist as a valid combination (i.e. one that exists in the junction table), it can simply use a composite foreign key to reference album_artist:

FOREIGN KEY (album_id, artist_id) REFERENCES album_artist (album_id, artist_id)


It is only when you need to reference the junction table often (that is, from many tables) that I would argue the first approach, which allows you to keep a dedicated PK column in the junction table, would be more useful.