Two fields be unique, like a compund primary key 2

[elfuser]

Here is the table:

CREATE TABLE `fs_gallery` (
`gallery_id` int(10) unsigned NOT NULL auto_increment,
`visible_gallery_id` varchar(20) NOT NULL default '' UNIQUE,
`freesite_id` int(10) unsigned NOT NULL default '0',
`name` varchar(50) NOT NULL default '',
PRIMARY KEY (`gallery_id`),
KEY `freesite_id` (`freesite_id`),
CONSTRAINT `fs_gallery_ibfk_1` FOREIGN KEY (`freesite_id`) REFERENCES `fs_freesite` (`freesite_id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

As it is now my visible_gallery_id's must be unique or the database won't insert the record. I understand this is how UNIQUE works. UNIQUE will probably have to be removed...

I didn't realize until now what I really wanted is to have visible_gallery_id and freesite_id work like a primary key so i can have
duplicate visible_gallery_ids as long as in the same record has a different freesite_id.

I do not want to nix gallery_id and make them primary keys.

How can I make it so I can have records with duplicate visible_gallery_ids AS LONG as the records don't share the same freesite_id? freesite_ids need not be unique in this table. i just want to prevent two or more records from having the same visible_gallery_id and freesite_id, but you should be able to have many records having the same freesite_id but NOT the same visible_gallery_id.

I'm sure this is simple for those who are more experienced in DB design than me.
Thanks

 

[alvechurchdata]

PRIMARY KEY (gallery_id, freesite_id) should work.

Depends on the dialect of SQL you're using.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[elfuser]

OK, my table definition now looks like this:

CREATE TABLE `fs_gallery` (
`gallery_id` int(10) unsigned NOT NULL auto_increment,
`visible_gallery_id` varchar(20) NOT NULL default '',
`freesite_id` int(10) unsigned NOT NULL default '0',
`name` varchar(50) NOT NULL default '',
PRIMARY KEY (`visible_gallery_id`, `freesite_id`),
KEY `gallery_id` (`gallery_id`),
KEY `freesite_id` (`freesite_id`),
CONSTRAINT `fs_gallery_ibfk_1` FOREIGN KEY (`freesite_id`) REFERENCES `fs_freesite` (`freesite_id`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

...and it seems to work well. Is this the best way to do it?
Thanks