CODEWITHSUNDEEP
phpmysql
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25755

is_unique syntax in mysql?

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;

Upvotes: 1

Views: 356

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562659

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)
);

Upvotes: 1

Frankie
Frankie

Reputation: 25165

You must define that column as UNIQUE.

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

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 838716

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.

Upvotes: 1

Related Questions