DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
  1. DZone
  2. Data Engineering
  3. Databases
  4. Advanced Database Constraints: There Can Be Only One

Advanced Database Constraints: There Can Be Only One

Jens Schauder user avatar by
Jens Schauder
·
Mar. 28, 12 · Interview
Like (0)
Save
Tweet
Share
4.24K Views

Join the DZone community and get the full member experience.

Join For Free

One of the powers of RDBMS as we use them today are constraints. I use unique indexes, not null constraints and foreign keys on a regular basis and if you do any work with RDBMSs you probably do as well.

From time to time one comes at a point where you think “it would be nice to enforce this with some kind of constraint”, but you can’t. Well maybe you just didn’t try hard enough. This is the first in a little series of articles pointing out some tricks you can do with database constraints.

I’ll talk Oracle here since that is what I know best, but many of these things are possible in other systems as well.

Lets assume we have a person table and a hobby table which holds the hobbies of a person:

    CREATE TABLE person (
      id number PRIMARY KEY NOT NULL,
      name varchar2(200) NOT NULL
      );
     
    CREATE TABLE hobby (
      id number PRIMARY KEY NOT NULL,
      person_id number REFERENCES person NOT NULL,
      name varchar2(200) NOT NULL
      )

Just ignore that the hobby table probably needs some normalization. Now assume you want to flag the favorite hobby of a person and of course there can be only one favorite hobby per person.

You could create an additional table holding that information, linking person entries and hobby entries, with a unique constraint on the person_id. From a theoretical point of view this is the correct normalized way to do it. But it makes you add a complete table when you just need a flag. There is a somewhat denormalized way to do it, which is in cases like this one simpler to use.

    ALTER TABLE hobby
    ADD  is_favorite varchar2(1) UNIQUE;
     
    ALTER TABLE hobby ADD constraint check_is_favorite
    CHECK (is_favorite = 'Y');

The idea is to add a is_favorite column and make it unique, but also only allow a single value different from null using a check constraint. Since null values don’t get indexed you can have as many null values in a unique column as you want. This does look probmising, but there is a problem. The is_favorite column should only be unique per person, so we have to include the person_id. But now all the null values get indexed, since a unique key only ignores null values when all indexed columns are null. So we have to replace the null values with something unique. We can do that by creating a function based column which is identical to is_favorite for not null values and replaces the null values with the primary key (which of course is unique by definition). 

    ALTER TABLE hobby
    ADD is_favorite_unique AS (nvl(is_favorite, id));
     
    ALTER TABLE hobby ADD CONSTRAINT unique_favorite_hobby
    UNIQUE(person_id, is_favorite_unique);

If you remove the unique constraint from the is_favorite column you finally have the desired behavior:

    CREATE TABLE person (
      id number PRIMARY KEY NOT NULL,
      name varchar2(200) NOT NULL
      );
     
    CREATE TABLE hobby (
      id number PRIMARY KEY NOT NULL,
      person_id number REFERENCES person NOT NULL,
      name varchar2(200) NOT NULL
      );
     
    ALTER TABLE hobby
    ADD  is_favorite varchar2(1);
     
    ALTER TABLE hobby ADD constraint check_is_favorite
    CHECK (is_favorite = 'Y');
     
    ALTER TABLE hobby
    ADD is_favorite_unique AS (nvl(is_favorite, id));
     
    ALTER TABLE hobby ADD CONSTRAINT unique_favorite_hobby
    UNIQUE(person_id, is_favorite_unique);
     
    INSERT INTO person VALUES (1, 'Jens');
    INSERT INTO person VALUES (2, 'Alfred');
    INSERT INTO hobby (id, person_id, name, is_favorite) VALUES (10,1, 'coding', 'Y');
    INSERT INTO hobby (id, person_id, name, is_favorite) VALUES (20,2, 'soccer', NULL);
    INSERT INTO hobby (id, person_id, name, is_favorite) VALUES (21,2, 'reading', NULL);
    INSERT INTO hobby (id, person_id, name, is_favorite) VALUES (22,2, 'swimming', 'Y');
    – this fails with a unique exception
    – insert into hobby (id, person_id, name, is_favorite) values (23,2, 'watching highlander', 'Y');

 

 

Database

Published at DZone with permission of Jens Schauder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • AWS CodeCommit and GitKraken Basics: Essential Skills for Every Developer
  • Building a RESTful API With AWS Lambda and Express
  • [DZone Survey] Share Your Expertise and Take our 2023 Web, Mobile, and Low-Code Apps Survey
  • Public Key and Private Key Pairs: Know the Technical Difference

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: