I have a table in MySql Database. I would like to prevent inserting matching rows in MySql. Like I have 4 columns in a table. I would not like to insert any row which has matching values of these 4 columns. I am trying to show that below
My table
----------
product_name| product_sku |product_quantity| product_price
----------
Computer | comp_007 | 5 | 500
I would like to prevent to insert same row again. How can I do that using MySql Query ??
UPDATE
I would not like to insert again
Computer | comp_007 | 5 | 500
But I would like to insert below rows
mouse | comp_007 | 5 | 500
Computer | comp_008 | 5 | 500
Computer | comp_007 | 50 | 500
Computer | comp_007 | 5 | 100
mouse | mou_007 | 5 | 500
Create a combined unique key / composite key on the columns in question:
ALTER TABLE `table` ADD UNIQUE (
`product_name` ,
`product_sku` ,
`product_quantity`,
`product_price`
);
Any attempts to insert duplicate rows will result in a MySQL error.
If possible you should add a Unique Key to your columns:
ALTER TABLE `table_name`
ADD UNIQUE INDEX `ix_name` (`product_name`, `product_sku`, `product_quantity`, `product_price`);
and then use INSERT IGNORE:
INSERT IGNORE INTO table_name (product_name, product_sku, product_quantity, product_price) VALUES (value1, value2, value3, value4);
If the record is unique MYSQL inserts it as usual, if the record is a duplicate then the IGNORE keyword discards the insert without generating an error.
The simplest way would be to make your columns unique. On undesired inserts, your MySQL driver should throw an exception then, which you can handle.
From a domain logic point of view, this is a bad practice, since exceptions should never handle expected behaviour. By the way, your DB table could use a primary key.
So, to have a better solution, your approach could be: - Define a unique field (the SKU seems suitable); think about using this as the primary key as well - With MySQL, use a REPLACE statement:
REPLACE INTO your_tablename
SET product_name='the name'
-- and so on for other columns
WHERE product_sku = 'whatever_sku';
REPLACE does the Job of trying to INSERT, and doing an UPDATE instead if the PK exists.