How to add a constraint to an object-CodePudding

to execute the code : https://dbfiddle.uk/?rdbms=oracle_21&fiddle=69131953cf61459b64092025737d79b7

I have a object that has several field. And I want to say that a field can have only certain values.

I tried to do that the same way I would do it with a table

create type oa as object(
  a VARCHAR2(59) constraint cta check(a in ('a1','a2' ))
)

ORA-24344: success with compilation error

I have tried to create a table and say that my object has the same fields

CREATE TABLE ta(
        a VARCHAR2(59) constraint cta check(a in ('a1','a2' ))
    )

Create type oa2 as  ta%rowtype

ORA-24344: success with compilation error

It doesn't work either.

CodePudding user response：

You cannot; constraints can only be applied to tables or views.

From the constraint documentation:

Constraint clauses can appear in the following statements:

CREATE TABLE (see CREATE TABLE)

ALTER TABLE (see ALTER TABLE)

CREATE VIEW (see CREATE VIEW)

ALTER VIEW (see ALTER VIEW)

As an alternative, you can declare the type as:

CREATE TYPE oa as object(
  a VARCHAR2(59)
);

Then declare an object-derived table with an added CHECK constraint:

CREATE TABLE oat OF oa (
  CONSTRAINT oat_chk CHECK (a in ('a1', 'a2'))
);

Then:

INSERT INTO oat VALUES (oa('a1'));
INSERT INTO oat (a) VALUES ('a1');

works but:

INSERT INTO oat VALUES (oa('b1'));
INSERT INTO oat (a) VALUES ('b1');

Violates the check constraint.

db<>fiddle here