check if duplicate value is being added to mysql table-CodePudding

I am creating a program to practice MySQL where there is a table called inventory and the user adds to the table

item_code   item_name    price   quantity 
a000        a            100     100

I want to make it so that if the user inputs a000 then he gets a message of the item_code already being in the table is there any way to do this

CodePudding user response：

You can specify a UNIQUE index on the item_code field.

CREATE UNIQUE INDEX item_code_unique
ON inventory(item_code);

Using MySQL UNIQUE Index To Prevent Duplicates

CodePudding user response：

Try this:

import sqlite3
conn = sqlite3.connect("NameOfYourDatabase.db")
cur = conn.cursor()
cur.execute("""CREATE TABLE IF NOT EXISTS inventory (
                      item_code text UNIQUE,
                      item_name text,
                      price INT,
                      quantity INT
                      )"""
try:
   #INSERT whatever you want into the db here 

except sqlite3.IntegrityError:
   print("Item code already exists")

You can also make your item_code a PRIMARY KEY as a PRIMARY KEY is automatically set as UNIQUE

REMEMBER: You can have only one PRIMARY KEY per table.

If your table is already created:

ALTER TABLE inventory
   MODIFY item_code text NOT NULL UNIQUE;

With PRIMARY KEY:

ALTER TABLE inventory
ADD PRIMARY KEY (item_code);

Learn more in this website:

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