is_unique syntax in mysql?

Question

I have a table entity called position. I am storing the value using smallint(2) datatype, where I intend to store the value from 0 to 9 (max). I want to make sure the value is just a single integer and an integer should not be repeated at any cost. There should be a unique value assigned to each id of the position entity in the table ranging from 0 to 9. How do I achieve it? Is there any mysql syntax for that?

I am using MySQL with PHP.

here is the mysql table could someone add me the unique code and show

CREATE TABLE `advertisements` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL,
`position` smallint(2) NOT NULL,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

is this the correct syntax?

CREATE TABLE `advertisements` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL,
`position` smallint(2) NOT NULL UNIQUE,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

Your question has two parts:

• How do I ensure that a value is unique in a column?

Add a unique index.

• How do I restrict a value to be in the range 0 to 9?

You can add a foreign key constraint to a table that lists the allowed values.

Answer 2

You must define that column as UNIQUE.

ALTER TABLE `a` ADD UNIQUE (
    `id`
)

Answer 3

First of all, smallint(2) and smallint(1234) and smallint are exactly the same in MySQL. The numeric argument is not a limit on the length or the range of values, it's only a hint for display width. MySQL's smallint is a 16-bit integer, with a range from -32768 to 32767. smallint unsigned has a range from 0 to 65535.

You can ensure no duplicates exist in a column by declaring a PRIMARY KEY or a UNIQUE KEY:

CREATE TABLE MyTable (
  position SMALLINT NOT NULL,
  UNIQUE KEY (position)
);

To restrict the values to the range 0 to 9, there are a few options:

• Write a trigger that causes an error if you try to store a value outside the range.

  DELIMITER //
  CREATE TRIGGER ZeroToNine BEFORE INSERT ON MyTable
  FOR EACH ROW
  BEGIN
      DECLARE p SMALLINT;
      IF (NEW.position NOT BETWEEN 0 AND 9) THEN
          SET p = 'Position must be between 0 and 9';
      END IF;
  END //
  DELIMITER ;
  

• Use a foreign key constraint so the column references a table that you populate with the values 0 to 9.

  CREATE TABLE ZeroToNine (
    position SMALLINT NOT NULL PRIMARY KEY
  ) ENGINE=InnoDB;
  INSERT INTO ZeroToNine VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
  
  
  CREATE TABLE MyTable (
    position SMALLINT NOT NULL,
    UNIQUE KEY (position),
    FOREIGN KEY (position) REFERENCES ZeroToNine (position)
  ) ENGINE=InnoDB;
  

• Other databases also support a CHECK constraint, but MySQL unfortunately doesn't support this feature.

  CREATE TABLE MyTable (
    position SMALLINT NOT NULL
      CHECK (position BETWEEN 0 AND 9),
    UNIQUE KEY (position)
  );