DB2 Child Table Not Working - Create Table

Question

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!

Answer 1

A couple of comments, first:

1. A parent table must have a primary or unique key in order for it to be referenced by a foreign key.

2. Your post implies that you want BookTitles to be the child table (even though this seems backwards to me). Logically, I would think that you want the logic to be: "If you delete a book from the BookTitles table, then the rows from the TitleAuthors table should be deleted, too" -- not the other way around. From your description of the TitleAuthors table, the logical primary key is (TITLE_ID, AU_ID). This would preclude it from being a parent table to BookTitles, which does not contain AU_ID.

3. It's a lot easier to add constraints after creating a table than to try and specify it in the CREATE TABLE STATEMENT.

---

Assuming that you want your parent/child relationship reversed (BookTitles as parent, TitleAuthors as child):

CREATE TABLE BookTitles (
    title_id    char(6) NOT NULL,
    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
);

alter table booktitles 
   add primary key (title_id);

alter table titleauthors
   add constraint fk_booktitles
   foreign key (title_id) 
   references booktitles (title_id)
    on delete cascade;

This will cause records in TitleAuthors to be deleted if the title is deleted from BookTitles.