topic Re: Database table and Unique key in Application Development and Automation Discussions

Database table and Unique key

former_member24570 — Mon, 06 Nov 2006 09:01:46 GMT

Hello All,

I have a database (customizing) table with two columns; one of them is a primary key and the other column should have the following behavior: Entering data for this column is optional. However, if some data is entered, it should be unique when compared to the existing values in that column. I tried unique index on this column but the problem is that multiple rows cannot have this column left unfilled. So, can anybody tell me how I can achieve this?

Regards,

Santhosh N

Re: Database table and Unique key

anversha_s — Mon, 06 Nov 2006 09:04:08 GMT

Hi Santhosh,

in the code level of the pgm u can do this.

Check the database for the exixting values befor u insert a new value to that field in DB from the pgm.

If it exists dont allow to enter, other wise allow.

rgds

Anver

if hlpd pls mark points

Re: Database table and Unique key

Former Member — Mon, 06 Nov 2006 09:08:26 GMT

Hi santhosh,

U can give a check table to that particular field. so that the field becomes unique by assigning the cardinality 1:1. and maintain the required unique values in the check table.

santhosh

Re: Database table and Unique key

former_member24570 — Mon, 06 Nov 2006 09:14:15 GMT

Hello,

I am more interested in have a check done by the database itself rather than a program. Generally speaking, a unique key defined on this should solve the problem. But I do not know whether this can be done in ABAP. Secondly, the content of this field is unknown.

Regards,

Santhosh N

Re: Database table and Unique key

Former Member — Mon, 06 Nov 2006 10:26:03 GMT

Hi Santhosh,

As you mentined you have 2 fields in your table, one is primary key and other is a simple field.

this check can not be done by the database as this second field is not a primary key and as you said the content of this field can be anything and there can't be a check table for the same, even if you have a check table then you can not avoid the repetation of the same content for this field.

Now the only solution left is to do it programatically.

For eg: when ever user inserts a new entry into the database you can put a check


  select * from <table> 
     into table itab.
  read table itab into wa
      with key <field2> = user_input.
   if sy-subrc = 0.
      error message "this entry already exist".
   else.
     insert user_input into <table>.
   endif.

hope this helps.

Regards,

kinshuk

Re: Database table and Unique key

Former Member — Mon, 06 Nov 2006 11:51:17 GMT

As saxena said, you cannot do it directly from DB level. Either you have to achieve it thru' programming or keep both fields as key field.