How to declare a constraint to limit the number of holding foreign object?

Question

If I have 2 table, Owner(oid, name) Dog(name, oid), where foreign key Dog (oid) references Owner (oid)

I want to know how to set a constraint so that Owner can only own at most 2 Dogs?

Answer 1

Here is an example of what you are looking for with a trigger which blocks insertion of a third dog for the same owner.

create table owner(
id int primary key,
name varchar(10) );

create table dog(
name varchar(10) primary key,
oid int ,
constraint fk_dog_owner foreign key 
     (oid) references owner(id) );

CREATE OR REPLACE  TRIGGER dog_max_two
BEFORE INSERT ON dog
    FOR EACH ROW 
DECLARE
  dogCount number(10);
BEGIN 
  Select count(name) INTO dogCount
  From dog where oid = :NEW.oid;
  IF( dogCount > 1 )
    THEN
      RAISE_APPLICATION_ERROR( -20001,
        'maximum 2 dogs per person');
  END IF;
END;
/

INSERT INTO owner VALUES (1,'Bill');

INSERT INTO dog VALUES ('Rover', 1);

INSERT INTO dog VALUES ('Boy', 1);

INSERT INTO dog VALUES ('Alfie', 1);

ORA-20001: maximum 2 dogs per person

ORA-06512: at "FIDDLE_HZTJGHLANPRDIFXWPMZS.DOG_MAX_TWO", line 8 ORA-04088: error during execution of trigger 'FIDDLE_HZTJGHLANPRDIFXWPMZS.DOG_MAX_TWO'

select * from dog;

NAME  | OID
:---- | --:
Rover |   1
Boy   |   1

db<>fiddle here