0

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

hari
  • 35
  • 6

2 Answers2

1

You can specify a UNIQUE index on the item_code field.

CREATE UNIQUE INDEX item_code_unique
ON inventory(item_code);

You can then use a try-catch block to catch any error from inserting duplicates.

try:
    cursor.execute("INSERT INTO ....")
except MySQLdb.IntegrityError:
    print("Duplicate entry")

See also: How to avoid duplicate entries in a MySQL database without throwing an error

Using MySQL UNIQUE Index To Prevent Duplicates

Timothy Wong
  • 689
  • 3
  • 9
  • 28
Mike Holcomb
  • 403
  • 3
  • 9
0

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

Shwetha
  • 106
  • 8