Skip to Main Content
  • Questions
  • How to ensure that both parent and child rows are created in 1:1 relationship

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jess.

Asked: September 06, 2016 - 12:33 pm UTC

Last updated: November 16, 2020 - 2:05 am UTC

Version: 10gR2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

One of our applications has a basic 'customer' type schema: customer table, address, emails, orders, etc. At the heart of it is 'customer' itself defined with the following (all not null)
id int (pk),
customer_type char(1),
customer_unique_id char(10),
fk_inbound_feed_id int,
fk_contact_detail_id int,
fk_billing_agency_id int,
... [10 more FKs that define customer attributes]


A customer can be of 2 types: commercial or public. Because attributes of each are different, we've opted to keep each in a separate table: customer_commercial and customer_public. There is a 1:1 relationship between a record in 'customer' table and a given record in either of those tables--something we can't enforce, as 2-way FK would block inserts (unless you have advice on how to do this). We decided to keep 'customer' as main entity, so the other two tables are structured as:
id int (pk)
fk_customer_id int, (part-FK)
customer_type char(1), (part-FK)
... [individual attributes]


So customer_public (fk_customer_id, customer_type) is FK to customer(id, type).

We are not currently using the unique id as part of any key, just sequence-autogenerated IDs.

Previously the data was inserted by stored proc. For new records, it'd create contact info, billing, etc. records, then insert a new customer record, get its ID and then push the rest of the data down into either customer_public or customer_commercial.

There are several questions we've been stuck on with this:

1. Should we have gone the other way (i.e., inserting commercial/public records first and then rolling it up to 'customer')?
2. Should we be using the unique key instead as part of FK? (The key is unique globally, i.e., you could never have a public customer and commercial one with the same unique key)
3. How do we make sure that, after the code inserts the record into the first table ('customer' for the moment) that it also inserts corresponding records to the offshoots (comm/public)? In other words, is there a way to better enforce that 1:1 actually happens?

The reason for the 3rd question is that, in addition to feeds, some of this data is created in the GUI (at small 'corrective' volumes), and we've seen a few instances where the app code inserted into the 'customer' table but not into commercial/public. What can we do on the DB level to ensure this doesn't continue to happen?

Many thanks in advance--apologies if this is a bit roundabout...


and Chris said...

1. As customer is the master entity I would start with this. But it comes down to how you design your foreign keys.

2. At the minimum you should have a unique constraint on customers.customer_unique_id!

What you need to consider is:

- Can you ever assign a new unique id to a customer? If so, you'll want to keep the surrogate key. This prevents the "cascading PK update" problem.

- How will you do lookups of customers and their associated tables?

Joining on long characters can be slower than integers. So this would favour sticking with the surrogate. But say people are searching for customer items (e.g. addresses, emails, ...) using the unique id. If you make this the primary key and use it in your child tables then you can avoid the join in the first place!

3. Oracle doesn't allow conditional FKs. i.e. one column which could point at one table or another based on some other value.

But you can enable this by creating extra columns on the parent. One per child table type, holding a copy of the primary key. The child tables can then have deferrable FKs pointing back to these. This guarantees the 1:1 relationship.

If you're on 11g you can do it with a combination of virtual columns and deferrable FKs. The process is:

- Create a virtual column for each customer type. This returns the customer_id if the row is of that type. It's null otherwise
- Create a unique constraint on each of these virtual columns
- Create a deferrable foreign key on the child tables back to the appropriate virtual column in the parent.

Here's an example with just public customers for brevity:

create table customers (
  customer_id   int not null primary key,
  customer_type varchar2(10) not null,
  public_customer_id int as (
    case when customer_type = 'PUBLIC' then customer_id end
  ),
  unique (public_customer_id, customer_type)
);

create table public_customers (
  customer_id   int not null primary key,
  customer_type varchar2(10) not null 
    check (customer_type = 'PUBLIC'),
  foreign key (customer_id, customer_type)
    references customers (public_customer_id, customer_type),
  unique (customer_id, customer_type)
);

alter table customers add constraint fk 
  foreign key (public_customer_id)
  references public_customers (customer_id)
  deferrable initially deferred;
  
insert into customers (customer_id, customer_type) values (1, 'PUBLIC');
commit;

SQL Error: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CHRIS.FK) violated - parent key not found
02091. 00000 -  "transaction rolled back"

insert into customers (customer_id, customer_type) values (2, 'PUBLIC');
insert into public_customers values (2, 'PUBLIC');
commit;

insert into customers (customer_id, customer_type) values (3, 'COMM');
commit;


You've said your on 10g. You can still do this. But the type_id columns in customers will have to be real columns. This makes it more convoluted and require more storage. But still possible:

alter table customers drop constraint fk ;
drop table public_customers purge;
drop table customers purge;
create table customers (
  customer_id   int not null primary key,
  customer_type varchar2(10) not null,
  public_customer_id int,
  check (
    ( customer_type = 'PUBLIC' and public_customer_id is not null and public_customer_id = customer_id ) or
    ( customer_type <> 'PUBLIC' and public_customer_id is null )
  ),
  unique (public_customer_id, customer_type)
);

create table public_customers (
  customer_id   int not null primary key,
  customer_type varchar2(10) not null 
    check (customer_type = 'PUBLIC'),
  foreign key (customer_id, customer_type)
    references customers (public_customer_id, customer_type),
  unique (customer_id, customer_type)
);

alter table customers add constraint fk 
  foreign key (public_customer_id)
  references public_customers (customer_id)
  deferrable initially deferred;
  
insert into customers (customer_id, customer_type) values (1, 'PUBLIC');
commit;

SQL Error: ORA-02290: check constraint (CHRIS.SYS_C005191) violated

insert into customers (customer_id, customer_type, public_customer_id) values (2, 'PUBLIC', 2);
commit;

SQL Error: ORA-02091: transaction rolled back
ORA-02291: integrity constraint (CHRIS.FK) violated - parent key not found
02091. 00000 -  "transaction rolled back"

insert into customers (customer_id, customer_type, public_customer_id) values (3, 'PUBLIC', 3);
insert into public_customers values (3, 'PUBLIC');
commit;

insert into customers (customer_id, customer_type) values (4, 'COMM');
commit;

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jess, September 06, 2016 - 4:17 pm UTC

Hi Chris,

Thanks for the reply--it's very useful!
Yes, there is a unique key on the customer unique id (and yes, it can be assigned to a new customer), so perhaps seq IDs are the way to go. Some parts of the system search by unique id, some by PK id depending on where they're coming from and what they have.

As for maintaining the 1:1 relationship, it's an interesting approach. But what if we want to split/add more types of customers? Part of the reason we chose the current approach is that we can just 'plug in' a new customer type table and only amend check constraint on the 'customer' table. In your example, if we wanted to have 10 different customer types, it translates into 10 new columns, which seems like the sort of thing that would have more implications on the functional code hitting the customers table... Or are we overthinking it? :)



Chris Saxon
September 07, 2016 - 9:21 am UTC

But what if we want to split/add more types of customers?

Yes, you need to add more columns. If you can use the virtual column method this is lightweight. Everything will work and there's no extra storage.

Sure, if you have select *s you'll be bringing back more columns. But you shouldn't be doing this anyway ;) And if you're on 12c and feeling brave you can avoid this by making them invisible!

On 10g you will have the overhead of writing code to maintain the new columns. But this is a one-off thing. If you follow the good practice of explicitly specifying columns in selects and inserts then functionally everything should be fine.

Another glorious example for SQL assertions.

Toon Koppelaars, September 07, 2016 - 11:15 am UTC

In a world with availability of SQL assertions, you wouldn't have had to have that redundant column CUSTOMER_TYPE in your separate tables. Also, those tables could just have the same ID column as CUSTOMER as their PK's, which at the same time would have an FK declared to them pointing back to CUSTOMER.

Then, to ensure the "1:1 inserts", just simply declare the following:

create assertion customer_public as check
(not exists
  (select 'public customer without detail data'
   from customer c
   where customer_type = 'P'
   and not exists(select 'detail data' from customer_public cp where cp.id = c.id))
)

create assertion customer_commercial as check
(not exists
  (select 'commercial customer without detail data'
   from customer c
   where customer_type = 'C'
   and not exists(select 'detail data' from customer_commercial cc where cc.id = c.id))
)

And finally:

create assertion customer_detail_once as check
(not exists
  (select id
   from customer_public
   intersect
   select id
   from customer_commercial)
)

Which can all be easily evolved when more customer-type's arrive.

If you like SQL assertions, vote for them:

https://community.oracle.com/ideas/13028

Chris Saxon
September 07, 2016 - 12:47 pm UTC

Amen :-)

Deferrable of course...

Toon, September 07, 2016 - 12:00 pm UTC

Minor ommission on prior post:

The first two assertions would of course have to be created with

DEFERRABLE INITIALLY DEFERRED

use types and/or nested tables

Joseph Charpak, September 07, 2016 - 4:36 pm UTC

Could the Original Poster use types (customer_t) and subtypes (customer_commercial_t and customer_public_t) and create a column of type customer_t in the customer table, but populate it with either instances of customer_commercial_t or customer_public_t ?

Something like the following:



CREATE   TYPE customer_t  AS OBJECT (customer_style varchar(50)) 
   NOT FINAL;
/

CREATE  TYPE customer_commercial_t UNDER customer_t 
   (some_attribute NUMBER) FINAL;
/

CREATE  TYPE customer_public_t UNDER customer_t 
   (some_other_attribute varchar2(10)) FINAL;
/


create table customer (customer_id number(10), customer_attributes customer_t);

insert into customer values(1,customer_commercial_t('commercial',123));

insert into customer values(2,customer_public_t('public','abc'));

commit;

SELECT treat(customer_attributes as customer_commercial_t) vals
  FROM customer
  where customer_attributes is of (customer_commercial_t);
  
  

SELECT treat(customer_attributes as customer_public_t) vals
  FROM customer
  where customer_attributes is of (customer_public_t);

Chris Saxon
September 07, 2016 - 7:43 pm UTC

I'm sure you could. But I wouldn't recommend it. Nested tables are harder to work with than regular tables. Particularly if you want to have tables that are children of just one customer type.

Jess, November 13, 2020 - 8:39 pm UTC

Dear Masters,

Is there a way to use deferred constraints (or another mechanim other than a trigger) to enforce a bi-directional relatioship that's one-to-many and many-to-one? In other words, every child must have a parent (obviously), but every parent must also have a child?

For example:
create table product_category
( id number, 
  name varchar2(50),
  owner varchar(10));

alter table product_category add constraint pk_id primary key (id);

create table products
( category_id number,
  product_name varchar2(50),
  price number);

alter table products add constraint uk_category_product unique (category_id, product_name);

alter table products add constraint fk_product_category foreign key (category_id) references product_category(id);

insert into product_category values (1, 'greeting cards', 'joe'); 
insert into product_category values (2, 'flowers', 'sam'); 

insert into products (2, 'daisies', 0.50);
insert into products (2, 'carnations', '0.75');

commit;


But what if we want to say that you are not allowed to create a product category unless you have something to put into it? In other words, this commit should fail unless you're also inserting rows for id 1 into the category table?

Trying a foreign key on product_cateogry "references products(category_id)" is obviously not right because the unique key on products is the combination of category_id/product_name, so there is a mismatch.

What is the best way to enforce this kind of bi-directional relationship?

Thank you!
Connor McDonald
November 16, 2020 - 2:05 am UTC

Take a look here

https://asktom.oracle.com/pls/apex/asktom.search?tag=preventing-childless-parent-records

It has a trigger approach, but also a link to the materialised view approach.

A reader, November 16, 2020 - 4:14 pm UTC

Thanks Connor, not sure how I'd missed that!