Is it possible to create a table with refs to rows in a other table in oracle sql?-CodePudding

I am implementing a small database in oracle sql to store infos about song and artists

create type song_t as object
(
    SONGID INTEGER,
    TITLE varchar2(100),
    GENRE varchar2(100),
    DATE_OF_CREATION date,
    BPM NUMBER 
);

create type artist_t as object
(
    ARTISTID INTEGER,
    NAME varchar2(100),
    SURNAME varchar2(100),
    DATE_OF_BIRTH date
);

create table SONG(
    SONGID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY not null,
    TITLE varchar2(100),
    GENRE varchar2(100),
    DATE_OF_CREATION date DEFAULT sysdate,
    BPM NUMBER 
);
/
create table ARTIST(
    ARTISTID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY not null,
    NAME varchar2(100) not null,
    SURNAME varchar2(100) not null,
    DATE_OF_BIRTH date
);

create table ARTIST_SONG_CONNECTION(
    SONG_C REF song_t not null,
    ARTIST_C REF artist_t not null
);

So what I want to do is to make a procedure, that inserts a new song of an artist knowing his id and make a reference to a particular song and a particular artist that are located on their tables.

Is it possible to do that with such a database structure, or do I need to make a table having objects like CREATE TABLE SONGS_REF OF song_t?

CodePudding user response：

To me, it looks like an ordinary referential integrity issue - just create a foreign key from song table to artist, including a new column (artistid) into the song table.

I don't think you need any additional type (while you created two of them).

SQL> create table artist(
  2      artistid integer generated always as identity primary key not null,
  3      name varchar2(100) not null,
  4      surname varchar2(100) not null,
  5      date_of_birth date
  6  );

Table created.

SQL> create table song(
  2      songid integer generated always as identity primary key not null,
  3      title varchar2(100),
  4      genre varchar2(100),
  5      date_of_creation date default sysdate,
  6      bpm number,
  7      --
  8      artistid integer constraint fk_song_art references artist (artistid)
  9  );

Table created.

Procedure:

SQL> create or replace procedure p_insert_song
  2    (par_title    in song.title%type,
  3     par_genre    in song.genre%type,
  4     par_date     in song.date_of_creation%type,
  5     par_bpm      in song.bpm%type,
  6     par_artistid in song.artistid%type
  7    )
  8  is
  9  begin
 10    insert into song (title, genre, date_of_creation, bpm, artistid)
 11      values (par_title, par_genre, par_date, par_bpm, par_artistid);
 12  end;
 13  /

Procedure created.

SQL>

CodePudding user response：

If you are going to use object then use object-derived tables:

CREATE TYPE artist_t AS OBJECT
(
    ARTISTID      INTEGER,
    NAME          varchar2(100),
    SURNAME       varchar2(100),
    DATE_OF_BIRTH date
);

CREATE TYPE song_t AS OBJECT
(
    SONGID           INTEGER,
    TITLE            varchar2(100),
    GENRE            varchar2(100),
    DATE_OF_CREATION date,
    BPM              NUMBER,
    artist           REF artist_t
);

Then you can create the tables:

CREATE SEQUENCE artist_seq;

CREATE SEQUENCE song_seq;

CREATE TABLE artist OF artist_t(
  artistid      DEFAULT artist_seq.NEXTVAL
                PRIMARY KEY,
  name          NOT NULL,
  surname       NOT NULL,
  date_of_birth NOT NULL
);

CREATE TABLE song OF song_t(
    SONGID           DEFAULT song_seq.NEXTVAL
                     PRIMARY KEY
                     NOT NULL,
    DATE_OF_CREATION DEFAULT sysdate
);

ALTER TABLE song ADD SCOPE FOR (artist) IS artist;

However, you cannot specify an IDENTITY column for object-derived tables (but you can use DEFAULT with a sequence).

Update

If you want artist_song_connection details then you can use a VIEW from the song table:

CREATE VIEW artist_song_connection (song, artist) AS
SELECT REF(s),
       s.artist
FROM   song s;

fiddle