Unique problem

Hello,
Im Creating a soccer database. Im create my gameplan out of my Teams table. On table Game i set Home and away Team on unique. Now im use a Procedure to fill games per gameday in the table Game. Problem is here sql cremte Game 1 Dortmund – Bayern and next game is Bayern – Dortmund on the Same gameday. How Can I set unique That this Cant be done?

 

Did not completely understood what you are trying to do, but if you want a unique team on a particular gameday, then your fill procedure must do that check before inserting record of another team on same gameday.

 

Problem is. Gameday 1
Dormunt-Bayern (Fine)
Bayern - Dortmund (not fine)
Köln - Gladbach (Fine)
Gladbach - Köln (Not Fine)

With 4 team there should be only 2 game per gameday.

so with unique I didn’t insert all other matches. But there the unique didn’t work

 

this is my code

#Temporary table
create temporary table alleSpiele as ( select t1.name as heim, t2.name as gast from (select Name from Verein) as t1, (select Name from Verein) as t2 where t1.Name != t2.Name);

#unique
create temporary table alleSpiele as ( select t1.name as heim, t2.name as gast from (select Name from Verein) as t1, (select Name from Verein) as t2 where t1.Name != t2.Name);

#procedure
delimiter //

create procedure spieltag (in spieltag int)
BEGIN

declare finished int default 0;
declare ende int default 0;
declare team1 varchar(45) default null;
declare team2 varchar(45) default null;

declare vereinscursor cursor for select Heim, Gast from alleSpiele;
declare continue handler for not found set finished := 1;
declare continue handler for 1062 set ende := 1;

open vereinscursor;

repeat
fetch vereinscursor into team1, team2;

if finished = 0 then
insert into Spiel (Spieltag, Datum, Uhrzeit, Heim, Gast) values (spieltag, current_date(),'15:30',team1,team2);
end if;
until finished = 1
END repeat;

close vereinscursor;

END//

 

this is the result