Home » SQL & PL/SQL » SQL & PL/SQL » Parent/Child Table Design Help (Oracle, 12.2.0.1.0, Linux)
Parent/Child Table Design Help [message #686755] |
Tue, 20 December 2022 08:51 |
Duane
Messages: 563 Registered: December 2002
|
Senior Member |
|
|
I'm looking for some help with designing Parent/Child tables. I'm trying to decide if the Child table should have a foreign key or not.
Or, basically, what’s best practice. I hardly ever create any tables so this is a little bit out of my comfort zone.
create table student_order(
id number,
term number,
order_id number,
amount number,
order_date date default sysdate);
create unique index student_order_pk on student_order
(id, term, order_id);
alter table student_order add (
constraint student_order_pk
primary key
(id, term, order_id)
using index student_order_pk
enable validate);
create table student_order_items
(id number,
term number,
order_id number,
order_item_id number,
title varchar2(50 char),
quantity number,
amount number);
create unique index student_order_items_pk on student_order_items
(id, term, order_id, order_item_id);
alter table student_order_items add (
constraint student_order_items_pk
primary key
(id, term, order_id, order_item_id)
using index student_order_items_pk
enable validate);
alter table student_order_items add (
constraint student_order_items_fk
foreign key (id, term, order_id)
references student_order (id, term, order_id)
enable validate);
OR
create table student_order(
id number,
term number,
order_id number,
amount number,
order_date date default sysdate);
create unique index student_order_pk on student_order
(id, term, order_id);
alter table student_order add (
constraint student_order_pk
primary key
(id, term, order_id)
using index student_order_pk
enable validate);
create table student_order_items
(order_id number,
order_item_id number,
title varchar2(50 char),
quantity number,
amount number);
create unique index student_order_items_pk on student_order_items
(order_id, order_item_id);
alter table student_order_items add (
constraint student_order_items_pk
primary key
(order_id, order_item_id)
using index student_order_items_pk
enable validate);
|
|
|
Re: Parent/Child Table Design Help [message #686757 is a reply to message #686755] |
Wed, 21 December 2022 01:17 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I think (not sure) your confusion is because you are trying to use natural keys. If you create and use surrogate keys instead, then it might become clear. If you Google around, you'll find many articles discussing the difference and why (in my opinion) surrogate keys are usually a better solution.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:36:36 CDT 2024
|