Forum Stats

  • 3,826,733 Users
  • 2,260,699 Discussions
  • 7,897,065 Comments

Discussions

duplicate name

af_2017
af_2017 Member Posts: 8 Red Ribbon

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.

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond
    Answer ✓

    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).

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond

    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
    af_2017 Member Posts: 8 Red Ribbon

    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
    Frank Kulash Member, Moderator Posts: 42,064 Red Diamond
    Answer ✓

    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
    af_2017 Member Posts: 8 Red Ribbon

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