[RESOLVED] Don't allow duplicates on a table in SQL

[h4x0rmx]

I have a simple table on my database:

CREATE TABLE `historical_price` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `symbol_id` int(11) DEFAULT NULL,
  `date_` date DEFAULT NULL,
  `price` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `symbols` (`symbol_id`),
  CONSTRAINT `symbols` FOREIGN KEY (`symbol_id`) REFERENCES `symbols` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

The hh:mm:ss in the date will always be the same.
I only need one price for every (symbol_id, date) pair. A lot of queries are going to try to INSERT data already existing in the table, but I don't want to allow it.
I know I can run a SELECT query to check if there's the same data on the table already, but I believe that you can avoid this extra-check designing the table right.

Does anyone have an idea of how to do it (if possible)?
I'll appreciate your help!

[bradgrafelman]

You could add a UNIQUE key constraint to those two columns. This would cause those INSERT queries to fail due to error 1062 (duplicate key for data).

[h4x0rmx]

Thanks! that's exactly what I wanted to do! 🙂