how to create a unique key over two fields.

Report · Feb 09, 2010

I need to create a unique key over two fields, (from and to), both of which are foreign keys. The primary key is offer_id, which is unique.

The reason for doing this is that an 'offer' should never contain the same from AND to numbers, although it is ok to contain the same from OR to numbers.

for example if a from/to of 29/30 exists

29/31 is ok
34/30 is ok
34/29 is ok
29/30 is not ok
30/29 is not ok

How do i set this up in the database?

I am using MySQL phpadmin and have recently downloaded the workbench.

Here the data layout.

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 09, 2010 at 07:17 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @Deleted User_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @Deleted User_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @Deleted User_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `guitarswap`
--

-- --------------------------------------------------------

--
-- Table structure for table `offer`
--

CREATE TABLE IF NOT EXISTS `offer` (
`offer_id` int(10) NOT NULL AUTO_INCREMENT,
`from` int(10) NOT NULL,
`to` int(10) NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`status` varchar(10) NOT NULL,
PRIMARY KEY (`offer_id`),
KEY `from` (`from`),
KEY `to` (`to`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

--
-- Dumping data for table `offer`
--

INSERT INTO `offer` (`offer_id`, `from`, `to`, `date`, `status`) VALUES
(2, 32, 29, '2010-02-08 19:32:28', 'proposal'),
(3, 31, 29, '2010-02-08 19:31:59', 'proposal'),
(9, 30, 29, '2010-02-08 19:27:30', 'proposal'),
(10, 32, 30, '2010-02-08 19:36:09', 'proposal'),
(11, 30, 29, '2010-02-08 19:40:33', 'proposal'),
(12, 30, 29, '2010-02-08 19:40:34', 'proposal'),
(13, 30, 29, '2010-02-08 19:40:35', 'proposal'),
(14, 30, 29, '2010-02-08 19:40:35', 'proposal'),
(15, 32, 31, '2010-02-08 19:56:48', 'proposal');

--
-- Constraints for dumped tables
--

--
-- Constraints for table `offer`
--
ALTER TABLE `offer`
ADD CONSTRAINT `offer_ibfk_1` FOREIGN KEY (`from`) REFERENCES `advert` (`advert_id`),
ADD CONSTRAINT `offer_ibfk_2` FOREIGN KEY (`to`) REFERENCES `advert` (`advert_id`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Report · Feb 11, 2010

The normal way to do this is to create a composite primary key of the two numbers. So, instead of offer_id being your primary key, the from and to columns together are treated as the primary key. Because you are using foreign keys in those columns, they must not be set to auto_increment.

What makes your scenario difficult is that you don't want the same combination in either order. The only way I can think of handling that is to check for both combinations before inserting a new record. If neither exists, insert the correct order with whatever status you want, and change the status column to permit NULL values. Set the opposite combination's status to NULL.

Report · Feb 11, 2010

I agree with David. I don't think there's any way to enforce this in the DBMS. Your application code would need to enforce it. This makes me think that you may have a problem with your database design.

Report · Feb 12, 2010

Thanks for all your help, again.

I did think about having a composite primary key, but I wanted the user to be able to click on a single 'offer_Id', rather than a from and to, which would be more confusing, so keeping this and checking for duplicates in dreamweaver seemed like a good compromise.

The 'from' and 'to' fields are indexed and it's a only a university project so any speed issues caused by the need to search for duplicates is not a problem.

I also thought I may need a re-think on my database design, but the bottom line is it works, so why let the database design dictate the project?

Thanks again.