Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Two fields be unique, like a compund primary key 2

Status
Not open for further replies.

elfuser

Programmer
Feb 22, 2005
3
CA
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top