Unique album name for his author

Question

I am trying to make two tables: one for the author and one for their music album.

Author can't have more than one album with the same name. It should work once per author ID / name / whatever.

This should NOT be allowed:

Author | Album name
-------------------
Johny  | FireGrasp
Johny  | FireGrasp

This should BE allowed:

Author | Album name
-------------------
Johny  | FireGrasp
Mandy  | FireGrasp

Should I make procedure for it?

I will try to explain it by how code should work:

insert into Author(Val) VALUES ('John'), ('Mandy');

-- Author_id: 3=John, 4=Marx
insert into Album(Val, Author_id) VALUES ('Firegrasp', 3);
insert into Album(Val, Author_id) VALUES ('Firegrasp', 3); -- This should not work now
insert into Album(Val, Author_id) VALUES ('Firegrasp', 4); -- This should work

Answer 1

Not sure whether You want to create a table (OR) you looking for query to get the posted output.

If you are looking for creating a table then you can make Author,Album name as primary key (composite PK) which will make sure that combination of Author + Album name will always be unique. primary key(Author, [Album name])

You can even create a multicolumn unique constraint like unique(Author, [Album name]) in case you already have a PK defined in your table.

(OR)

If you are looking for a query for getting those desired result then I think you can simply use distinct keyword which will get you only one of those two record.

Considering, your Album table has only this two columns (Val, Author_id); create a primary key like

create table Album
(Val varchar(20),Author_id int,
CONSTRAINT PK_composite_123 PRIMARY KEY CLUSTERED (Val, Author_id))