Unique combination of values between tables

[edpatterson]

I tried searching on unique and found lots of good information, but nothing I could adopt to my needs.

What I have

Tables:
Sites
siteID int(11) not null, auto_increment
name varchar(30) not null

Rooms
roomID int(11) not null, auto_increment
name varchar(20) not null,
siteID not null,
primary key (roomID)
foreign key (siteID) references sites (sitesID)

Obviously there will be multible room 100's in the table, I want to make sure that there is only one site X room 100.

Is is possible to use foreign columns in a key/index?

Thanks,
Ed

 

[r937]

please clarify, what does "there is only one site X room 100" mean?

what's a room 100?

yes, you can index a foreign key

r937.com | rudy.ca

 

[edpatterson]

Say I have a site named 'east' it has a room named 100.
I also have a site named 'west' with a room named 100.

With my current setup I could end up with multiple 'east'-'100' records.

What would the foreign key command be?

Ed

 

[r937]

okay, to answer your question "only one site X room 100" you could do this with a unique constraint

alter table Rooms
add unique only_one_room_name_per_site 
     (siteID, name)

however, this means only one room named anything per site, not just one room named 100 per site



r937.com | rudy.ca

 

[edpatterson]

So in effect it would limit all of my sites to 1 room? Not what I had in mind. I am simply trying to keep from having duplicate room numbers associated with a single site.

Maybe I will have to do a query or 2 to check for duplicates before I store the data.

Ed

 

[r937]

no, it does not limit all of your sites to one room

it limits all of your sites to only one room of each name

why don't you set up a table quickly and actually try it

also, checking for duplicates before storing values is 100%inefficient, and almost never necessaray

r937.com | rudy.ca

 

[edpatterson]

Thanks, this will work nicely.

It is a darn good thing I don't do database programming for a living

Ed