Adding a unique key to the oracle database table -

trying implement friendship table ..

to explain wat have done till

my ddl

<!-- working -- "relationship" - table used store relationship between users --> create table relationship(     relation_id number(8),     fromusername varchar2(30),     tousername varchar2(30),     statusid number,     senttime timestamp,     constraint relationship_pk primary key(relation_id),     foreign key (fromusername) references users(username),     foreign key (tousername) references users(username) );  <!--working add unique key 'relationship' table user can send request @ time user oncle -->  alter table relationship add constraint relation_unique unique (fromusername, tousername); 

here image explain problem

my problem have @ last 2 rows . .. users kamlesh1 send request jitu1 , again jitu1 sends request kamlesh1 , when kamlesh1 accepts request statusid changes 1 similar case kamlesh jitu when jitu accepts request. want prevent kind of duplication i.e once user has sent u request u cannot sent request him accept request or reject it. could'nt think of proper question title ...if u too.

please

you create unique function-based index this:

create unique index relation_unique on relationship ( least(fromusername, tousername), greatest(fromusername, tousername) ); 

a couple of side notes: don't need number (38 digits of precision) store value either 0 or 1. number(1) should suffice. also, don't need granularity of timestamp senttime - date should trick, , might make arithmetic bit easier (date arithmetic expressed in days, timestamp arithmetic in intervals). last, using camelcase column names in oracle isn't idea since oracle object names aren't case-sensitive unless enclose them in double quotes. if inspect data dictionary see columns this: fromusername, tousername. better use column names from_username , to_username (or username_from , username_to).