How to use INSERT INTO in database where a condition is the ID has to be unique-CodePudding

Probably confusing title but I'll explain.

I have this table named Shelf:

    CREATE TABLE Shelf (
  ID int(11) NOT NULL,
  UserID int NOT NULL NOT NULL,
  MovieID int NOT NULL,
  Nome varchar(255) NOT NULL,
  Image varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

And as you can see there is a field named MovieID, it's value is being incremented from an API and basically everytime I use this query "INSERT INTO Shelf VALUES (null,?,?,?,?)" if I try to add the same movie twice it will do it, and I don't want it to.

Succintly, I want to make sure that there is a verification in the SQL query that: if the MovieID (for a specific movie) is already stored in my database, I don't want it to be added again.

Thanks in advance!

CodePudding user response：

So the error was clear, I just needed to add a UNIQUE value to MovieID which now makes it look like so:

CREATE TABLE Shelf (
  ID int(11) NOT NULL,
  UserID int NOT NULL NOT NULL,
  MovieID int NOT NULL,
  Nome varchar(255) NOT NULL,
  Image varchar(255) NOT NULL,
  UNIQUE (MovieID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CodePudding user response：

Q: How to use INSERT INTO in database where a condition is the ID has to be unique

A: Apply UNIQUE, NOT NULL and PRIMARY KEY constraints to your column:

CREATE TABLE Shelf (
  ID int(11) UNIQUE NOT NULL PRIMARY KEY,
  UserID int NOT NULL,
  MovieID int NOT NULL,
  Nome varchar(255) NOT NULL,
  Image varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

https://www.w3schools.com/sql/sql_constraints.asp

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY - Prevents actions that would destroy links between tables

CHECK - Ensures that the values in a column satisfies a specific condition

DEFAULT - Sets a default value for a column if no value is specified

CREATE INDEX - Used to create and retrieve data from the database very quickly