How can I set a constraint on some of the fields excluded.

ALTER TABLE emp ADD CONSTRAINT no_duplicates UNIQUE ( dept, theme, date );

but there is also a deleted, deleted field, it is necessary that constraint only works on strings where the deleted = 0 field,

    2 answers 2

    You can make the deleted field an integer type. Then the "delete" procedure might look like this.

     UPDATE emp e SET e.deleted = (SELECT max(deleted) + 1 FROM emp i WHERE i.dept = e.dept AND i.theme = e.theme AND i.date = e.date) WHERE e.empid = in_empid;

    And you can do a unique constraint on four fields.

    If the application expects only 0 or 1 from the database in the deleted field, you can solve this using a view or query in a stored procedure that returns data.

     SELECT e.empid, e.empname, e.dept, e.theme, e.date, case e.deleted when 0 then 0 else 1 end deleted FROM emp e;
    • Unfortunately, this field can have only two values, 0 or 1. - J Mas
    • @JNat why only two? - 4per
    • this was set by the architecture, and the applications are already working with two values - J Mas
    • @JNat program can slip the presentation, where there will be only 0 and 1. - 4per
    • Yes, this is also a good idea, but the company does not use stored procedures, either it is just done on the backend or by the count, in the code I’ll just make a request and see if there is the same value. - J Mas

    We take advantage of the fact that indexes in Oracle do not store values ​​if the whole key is NULL and with a NULL key there can be as many values ​​as possible. Let's create such a unique functional index which for deleted = 0 will give the values ​​for the fields themselves, and for 1 it will give for all NULLs.

     create unique index emp_uniq on emp( decode(deleted,0,dept,NULL), decode(deleted,0,theme,NULL), decode(deleted,0,date,NULL) );