Can structured types or composite attributes within a structured type be UNIQUE?

Question

Can structured types or composite attributes within a structured type be UNIQUE?

For example, is this possible:

CREATE TYPE testType AS OBJECT (
  attr1 INTEGER,
  attr2 VARCHAR2(20),
  attr3 VARCHAR(20) UNIQUE
);

Answer 1

Can Structured Types or Composite Attributes within a Structured Type be UNIQUE?

No, the syntax does not allow that.

However, if you are using them in an object derived table that is when you can make columns (derived from the object attributes) unique.

CREATE TYPE testType AS OBJECT (
  attr1 INTEGER,
  attr2 VARCHAR2(20),
  attr3 VARCHAR(20)
);

CREATE TABLE test_table OF testType(
  attr3 CONSTRAINT test_table__attr3__u UNIQUE
);

Then:

INSERT INTO test_table (attr1, attr2, attr3 ) VALUES ( 1, 2, 3 );
INSERT INTO test_table (attr1, attr2, attr3 ) VALUES ( 4, 5, 3 );

Gives the error:

ORA-00001: unique constraint (SCHEMA_NAME.TEST_TABLE__ATTR3__U) violated

db<>fiddle here