How to insert multiple object rows into a table in oracle sql?-CodePudding

I need to implement the table below with sql (oracle):

First i created the types:

CREATE TYPE address AS OBJECT(num INT, street VARCHAR(20), city VARCHAR(20), zip INT);
CREATE TYPE phoneNum AS VARRAY(2) OF VARCHAR(13);
CREATE TYPE kids AS OBJECT(firstK VARCHAR(20), lastK VARCHAR(20), age INT);

Then i created the table "person":

CREATE TABLE person (id VARCHAR(10), firstName VARCHAR(20), lastName VARCHAR(20), adr address,pn phoneNum, ks kids);

I use this to insert into the table:

INSERT INTO person VALUES("1", "Justin", "Trudeau", address(10, "Main street", "Ottawa", 1000), phoneNum(" 123456789012"," 103647901456"), kids("Michelangelo", "Trudeau", 14));

Which works fine. But how do i insert the second row of "kids" (Leanardo, Trudeau, 10).

CodePudding user response：

how do i insert the second row of "kids" (Leanardo, Trudeau, 10).

You cannot. The kids type is defined as:

CREATE TYPE kids AS OBJECT(firstK VARCHAR(20), lastK VARCHAR(20), age INT);

and then you define the table as:

CREATE TABLE person (
  id VARCHAR(10),
  firstName VARCHAR(20),
  lastName VARCHAR(20),
  adr address,
  pn phoneNum,
  ks kids
);

kids (despite the plural in its name) is a singular object so you can only put a single kids value into it.

If you want to put multiple kids then you either need to:

change the table to store a collection of kids;
have a separate table for children; or
reverse the relationship and store two (or more) foreign key relationships for parents.

For example, you could (since children are people too) store the relationship as:

CREATE TYPE address AS OBJECT(
  num    INT,
  street VARCHAR2(20), 
  city   VARCHAR2(20),
  zip INT
);
CREATE TYPE phoneNum AS VARRAY(2) OF VARCHAR2(13);

CREATE TABLE person (
  id            INTEGER
                CONSTRAINT person__id__pk PRIMARY KEY,
  firstName     VARCHAR2(20),
  lastName      VARCHAR2(20),
  date_of_birth DATE,
  adr           address,
  pn            phoneNum
);

CREATE TABLE children(
  id        INTEGER
            CONSTRAINT children__id__pk PRIMARY KEY,
  parent_id CONSTRAINT children__parent_id__fk REFERENCES person (id),
  child_id  CONSTRAINT children__child_id__fk  REFERENCES person (id),
  CONSTRAINT children__pid_cid__u UNIQUE (parent_id, child_id)
);

db<>fiddle here