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!
