I have a bit of a task before me. (DB2 Database) I need to create a table that will be a child table (is that what it is called in SQL?) I need it so that it has a foreign key constraint with my other table, so when the parent table is modified (record deleted) the child table also loses that record.

Once I have the table, I also need to populate it with the data from the other table (if there is an easy way to UPDATE this).

If you could point me in the right direction, this would help alot, as I do not even know what syntax to look for.

Thanks in advance

The table I have in place:

create table titleauthors (
    au_id       char(11),
    title_id    char(6),
    au_ord      integer,
    royaltyshare    decimal(5,2));

The table I am creating:

create table titles (
    title_id    char(6),
    title       varchar(80),
    type        varchar(12),
    pub_id      char(4),
    price       decimal(9,2),
    advance     decimal(9,2),
    ytd_sales   integer,
    contract    integer,
    notes       varchar(200),
    pubdate     date);

I need the title_id to be matched with the title_id from the parent table AND use the ON DELETE CASCADE syntax to delete when that table is deleted from.

My Attempt:

CREATE TABLE BookTitles (
title_id    char(6) NOT NULL CONSTRAINT BookTitles_title_id_pk REFERENCES titleauthors(title_id) ON DELETE CASCADE,
title       varchar(80) NOT NULL,
type        varchar(12),
pub_id      char(4),
price       decimal(9,2),
advance     decimal(9,2),
ytd_sales   integer,
contract    integer,
notes       varchar(200),
pubdate     date)
;

Thanks in advance!