duplicate name

af_2017

i have a supplier master , where i have a primary key on supplier id column , but i need to put a check on supplier name as well for avoiding the duplicate names.

supplier id supplier name

1 Test

2 Test

in the above case 2 names are identical. we should avoid them.

Frank Kulash

Hi, @af_2017

i cannot put a unique constraint on this table , as i dont have rights to modify any table in ERP.

Can you create a unique index on the table?

If not, then create an INSTEAD OF trigger (or a procedure) and only allow INSERTs and UPDATEs through that trigger (or procedure).

Frank Kulash

Hi, @af_2017

i have a supplier master , where i have a primary key on supplier id column , but i need to put a check on supplier name as well for avoiding the duplicate names.

If you want to make sure than no two rows in a table have the same value, you can put a UNIQUE constraint on that column. For example

CREATE TABLE supplier_master
( supplier_id		NUMBER		PRIMARY KEY
, supplier_name	        VARCHAR2 (20)	UNIQUE
);

af_2017

Thanks @Frank Kulash , i cannot put a unique constraint on this table , as i dont have rights to modify any table in ERP. i am thinking of writing a pl/sql block and throwing some exception by comparing the given supplier name with that name that is existing in the table , something like upper(supplier_name) = upper(p_supplier_name).

Frank Kulash

Hi, @af_2017

i cannot put a unique constraint on this table , as i dont have rights to modify any table in ERP.

Can you create a unique index on the table?

If not, then create an INSTEAD OF trigger (or a procedure) and only allow INSERTs and UPDATEs through that trigger (or procedure).

af_2017

Thanks @Frank Kulash , i have never tried instead of trigger earlier , let me google and write it accordingly.