Adding unique data to a sql table

Question

I am learning about sql databases. I am using mysql.

I have designed the tables, and queries. I am now working on the code to put stuff into the database.

I can not work out how to ensure that a record is unique when I have a text field. I tried to mark the part of the record that was not the pk (primary key) as unique, but when it is text it complains that it is not fixed length. I then played with the idea of conditionals in a stored procedure, but could not get it to work.

DELIMITER $$

DROP PROCEDURE IF EXISTS `experiment1`.`add_zzzz`$$
CREATE PROCEDURE `experiment1`.`add_zzzz` (IN v INT, IN n TEXT)
BEGIN

IF EXISTS (
  SELECT value, name 
  FROM zzzz 
  WHERE value=v AND name=n
)
THEN
ELSE
  INSERT INTO zzzz(value,name)
  VALUES v,n;
END IF;

END$$

DELIMITER ;

So anyone know what I am doing wrong?

http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table — didierc, Apr 08 '13 at 11:30
http://stackoverflow.com/questions/15294934/best-way-of-keeping-text-field-unique-in-mysql-database — didierc, Apr 08 '13 at 11:34

score 0 · Answer 1 · answered Apr 08 '13 at 11:42

0

VARCHAR is not fixed and you can use unique index with it

more on http://www.w3schools.com/sql/sql_unique.asp

you can also use INSERT ignore

http://dev.mysql.com/doc/refman/5.5/en/insert.html

answered Apr 08 '13 at 11:42

Robert

17,808
4
50
79

Adding unique data to a sql table

1 Answers1