I have a table where every partner has an ID and a Steueridentifikationsnummer (tax identification number) StID.
The StID has to be unique in combination with the ID of the partner.
For example there can be more than one row with
ID 1 and StID 12345678910 but not another row with
ID 2 and StID 12345678910.
CREATE TABLE test_unique (id INTEGER,stid (VARCHAR2(11));
INSERT INTO test_unique (id, stid) VALUES (1,'12345678910');
INSERT INTO test_unique (id, stid) VALUES (1,'12345678910'); --ok
INSERT INTO test_unique (id, stid) VALUES (2,'12345678910'); – not ok
I know that this table is not normalized but it contains data collected from different sources for easier processing.
Is there a way to enforce this kind of uniqueness?
My first idea was a materialized view grouped by ID and StID with fast refresh on commit but it would be easier if I could get an error on a single insert and not on commit at the end of a lot of inserts.