Creating a Composite Key

create table FOO
( id smallint not null primary key identity
, name varchar(9)
)

insert into FOO ( name ) values ( 'curly' )
insert into FOO ( name ) values ( 'larry' )
insert into FOO ( name ) values ( 'moe' )


create table BAR
( id smallint not null primary key identity
, name varchar(9)
)

set identity_insert BAR on

insert into BAR ( id, name ) values ( 101, 'dog' )
insert into BAR ( id, name ) values ( 102, 'cat' )

set identity_insert BAR off

insert into BAR ( name ) values ( 'fish' )
insert into BAR ( name ) values ( 'gerbil' )  


create table FUBAR
( FOOid smallint not null 
, BARid smallint not null 
, foreign key ( FOOid ) references FOO ( id )
, foreign key ( BARid ) references BAR ( id )
, primary key ( FOOid, BARid )
)

insert into FUBAR values ( 1, 101)
insert into FUBAR values ( 1, 102)
insert into FUBAR values ( 1, 104)
insert into FUBAR values ( 3, 102)
insert into FUBAR values ( 3, 104)


select FOO.name as Stooge
     , BAR.name as Pet
  from FOO
inner
  join FUBAR
    on FOO.id = FUBAR.FOOid
inner
  join BAR
    on FUBAR.BARid = BAR.id
order
    by Stooge
     , Pet    

RESULTS:
    
Stooge Pet 
curly  cat
curly  dog
curly  gerbil
moe    cat
moe    gerbil