{"id":112978,"date":"2026-11-04T12:00:00","date_gmt":"2026-11-04T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=112978"},"modified":"2026-10-22T13:32:19","modified_gmt":"2026-10-22T13:32:19","slug":"2nf-and-bcnf-explained-the-whole-key-nothing-but-the-key","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/2nf-and-bcnf-explained-the-whole-key-nothing-but-the-key\/","title":{"rendered":"2NF and BCNF explained: the whole key, nothing but the key"},"content":{"rendered":"\n

‘Normal Forms’ are database normalization rules designed to prevent data duplication and editing anomalies in relational databases. <\/strong><\/p>\n\n\n\n

‘Second Normal Form’ (2NF) requires every column to depend on the entire<\/em> table key, not just part of it, while ‘Boyce-Codd Normal Form’ (BCNF) tightens this further so no column depends on anything other than the key. <\/strong><\/p>\n\n\n\n

The classic mnemonic \u2014 “the key, the whole key, and nothing but the key” \u2014 captures all three normal forms in one line. This guide breaks down what each rule actually<\/em> means, using worked examples you can apply to your own schema.<\/strong><\/p>\n\n\n\n

What are normal forms, and why do they matter?<\/h2>\n\n\n\n

Proper data modeling is crucial when designing a new database, and the first step in modeling is applying a set of table-structuring rules we call the ‘Normal Forms<\/strong>‘. <\/p>\n\n\n\n

These rules promote referential integrity, remove data redundancy, allow efficient querying, and help prevent editing anomalies. We covered the first of these rules, the aptly-named ‘First Normal Form<\/strong>‘, in part one<\/a> of this series.<\/p>\n\n\n\n

This is part two of Lee Asher’s complete guide series to the ‘Normal Forms’ set of database design rules<\/a>.<\/em><\/strong><\/p>\n\n\n\n

While ‘First Normal Form’ (1NF) is fairly easy to remember, the higher-order forms have formal definitions that seem designed to confuse. <\/p>\n\n\n\n

The goal of this article is twofold: first, to give you an easy, unforgettable way to remember these rules, and \u2013 more importantly \u2013 to show specifically why <\/em>these rules exist, and the problems you can avoid by following them.<\/p>\n\n\n\n

To recall these normal-form rules, just remember a simple mnemonic phrase, one that will be immediately familiar to any fan of legal dramas:<\/p>\n\n\n\n

The Key, the Whole Key, and Nothing But the Key<\/em><\/strong><\/p>\n\n\n\n

This phrase has three parts, representing three different rules. We’ll discuss each separately.<\/p>\n\n\n\n

\n
\n\n

You may also be interested in…<\/strong><\/p>\n\n\n\n

Joe Celko’s guide to database ‘Normal Forms’<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

The Key (every key must depend on the table’s key)<\/h2>\n\n\n\n

‘The Key’ rule simply means that every table needs a key, and every column in the table must depend on that key. In other words, the key value determines the value of all other data in the row.<\/strong><\/p>\n\n\n\n

Consider this table in a simple POS (point of sale) database, recording purchases at a retail shop:<\/p>\n\n\n\n

\"An<\/figure>\n\n\n\n

As you can see, sale_id<\/code> is the table’s primary key, and sale_amount<\/code>,\u00a0tax_amount<\/code>, and total<\/code> depend directly<\/em> on that key. <\/p>\n\n\n\n

However, the tax_rate<\/code> column does not<\/em> depend on the key. Static facts like this should be removed into a separate fact table, rather than duplicating that data throughout the entire table.<\/p>\n\n\n\n

This first rule is so simple it barely merits mention. The next one is a bit more complex.<\/p>\n\n\n\n

The Whole Key: Second Normal Form (2NF) explained<\/h2>\n\n\n\n

The ‘Whole Key’ rule requires that every table column must depend on the entire<\/em> key.<\/strong> <\/p>\n\n\n\n

That, in turn, only makes sense when a key has multiple parts – which brings us to the subject of multi-part keys<\/strong>. We’ll illustrate this with the table below, representing college course enrollments and final grades:<\/p>\n\n\n\n

\"An<\/figure>\n\n\n\n

Since a student can take the same course multiple times (once per term), the unique key<\/strong><\/a> for each row is the combination of three different columns: student_id<\/code>, course_id<\/code>, and term<\/code>. <\/p>\n\n\n\n

A student’s grade depends on all three of these, so it complies with our rule – but the instructor<\/code> column is only<\/em> dependent on two of them: course_id<\/code> and term<\/code>.<\/p>\n\n\n\n

As you can see from the repeated instructor name, this causes duplication of data and generates what’s known as an update anomaly<\/a><\/strong>. To change one fact \u2013 the instructor for each class \u2013 multiple rows must be edited. <\/p>\n\n\n\n

This impacts performance but, more importantly, raises the possibility that two rows referring to the same class might disagree on instructor<\/code>. <\/p>\n\n\n\n

The bottom line: a fact should only be stored in one location – referred to in data modeling as the ‘single source of truth’ (SSOT).<\/strong><\/p>\n\n\n\n

 We can solve this problem by decomposing the original table into two new ones:<\/p>\n\n\n\n

\"an<\/figure>\n\n\n\n

This ‘Whole Key’ rule is formally known as ‘2NF: Second Normal Form’.<\/strong> Now, you’ll never forget it.<\/p>\n\n\n\n

The three-part key for this table is rather cumbersome to use, so most shops would also add a surrogate key<\/strong><\/a> to identify each row (perhaps enrollment_id<\/code> or something similar). <\/p>\n\n\n\n

In fact, some shops add surrogates for all<\/em> tables, even when they have a single-column natural key<\/a>.<\/strong> <\/p>\n\n\n\n

However, that doesn’t change the fundamental point that a surrogate key stands in for the natural key; it doesn’t alter the column dependencies.<\/strong><\/p>\n\n\n\n

\n
\n\n

You may also be interested in…<\/strong><\/p>\n\n\n\n

How to find and track object references in SQL Server (guide to dependencies)<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

Nothing But the Key: Bryce-Codd Normal Form (BCNF) explained<\/h2>\n\n\n\n

The ‘Nothing But the Key’ rule requires that table columns only<\/em> depend on the table key itself.<\/strong> <\/p>\n\n\n\n

This time, we’ll start with a table that does<\/em> correctly conform to the rule. Our example is a medical research firm wishing to perform statistical analysis on the blood type of people who’ve been diagnosed with certain types of cancer. <\/p>\n\n\n\n

For each case, they store the patient blood type:<\/p>\n\n\n\n

\"an<\/figure>\n\n\n\n

Expressed in the functional dependencies<\/strong><\/a> of data modeling language, the rule for this table is simply:<\/p>\n\n\n\n

case_id<\/code>\u00a0\u00a0\u00a0\u00a0>>>\u00a0\u00a0 blood_type<\/code><\/p>\n\n\n\n

Don’t be put off by the notation here. The arrow simply means the value on the left determines the value on the right. So, if you know the case_id<\/code>, you also know the patient’s age.<\/p>\n\n\n\n

The firm later expands their database to include patient name and address to enable direct contact with the patient. They add two new columns to the table:<\/p>\n\n\n\n

\"An<\/figure>\n\n\n\n

This adds two new functional dependencies to the table:<\/p>\n\n\n\n

case_id<\/code>\u00a0\u00a0\u00a0\u00a0>>>\u00a0\u00a0\u00a0patient_name<\/code><\/p>\n\n\n\n

case_id<\/code>\u00a0\u00a0 >>>\u00a0\u00a0patient_address<\/code><\/p>\n\n\n\n

However, this list isn’t yet complete. Patient address and blood type also depend on the patient name:<\/p>\n\n\n\n

patient_name<\/code>\u00a0\u00a0 >>>\u00a0\u00a0\u00a0patient_address<\/code><\/p>\n\n\n\n

patient_name<\/code>\u00a0 >>>\u00a0\u00a0\u00a0\u00a0blood_type<\/code><\/p>\n\n\n\n

Now we have columns that are not only dependent on the table key, but also something else – so our rule is violated. To correct that violation, we must decompose patient data into a separate table. <\/p>\n\n\n\n

\n
\n
\n
\n

Subscribe to the Simple Talk newsletter<\/h3>\n
\n All the latest articles, guides, podcasts, and more – delivered straight to your inbox. <\/div>\n <\/div>\n Subscribe now<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

The update and delete anomalies BCNF prevents<\/h3>\n\n\n\n

Not correcting the violation would result in us having both an update anomaly<\/strong> and<\/em> a delete anomaly<\/strong>. <\/p>\n\n\n\n

The update anomaly would involve a patient whose cancer recurs appear twice in the table (potentially with different data each time).<\/p>\n\n\n\n

The delete anomaly, on the other hand, means that deleting a patient’s case would also remove the association between the patient name, address, and blood type.<\/p>\n\n\n\n

What about ‘Third Normal Form’?<\/h2>\n\n\n\n

You’ll notice we covered 2NF and BCNF, but what happened to ‘Third Normal Form’ (3NF)<\/strong>? Well, BCNF is essentially a slightly stricter version of 3NF’s big brother.<\/p>\n\n\n\n

The two forms actually function identically except in the unusual case where a table has multiple overlapping composite keys<\/a><\/strong>. In brief: a table in BCNF is by definition in 3NF, and nearly every table in 3NF is also<\/em> BCNF<\/strong>.<\/p>\n\n\n\n

What are the key differences between BCNF and 3NF?<\/h2>\n\n\n\n

The difference between the two forms is really very simple (though you’d never know it from most college courses on the subject). <\/p>\n\n\n\n

In 3NF, a column isn’t required to depend on the table key if that column itself is part of another key.<\/em><\/strong><\/p>\n\n\n\n

Let’s visualize this. <\/p>\n\n\n\n

A company has a skill-building program it encourages its employees to join, based across various teams. While an employee can join multiple<\/em> teams, they’re assigned just one mentor, and that mentor can only be assigned to one team – despite a team potentially having multiple mentors.<\/p>\n\n\n\n

Here’s some sample data:<\/p>\n\n\n\n

\"An<\/figure>\n\n\n\n

Expressed in the functional dependencies of data modeling language, the constraints for this table are:<\/p>\n\n\n\n

(employee, team)<\/code>\u00a0\u00a0>>>\u00a0\u00a0\u00a0mentor<\/code><\/p>\n\n\n\n

(mentor)<\/code>\u00a0\u00a0\u00a0>>>\u00a0\u00a0\u00a0team<\/code><\/p>\n\n\n\n

The second one is the problem: the team<\/code> column depends on mentor<\/code>, which is not<\/em> ‘the whole key’. Thus, the table is not<\/em> in BCNF. <\/p>\n\n\n\n

It is<\/em>, however, in 3NF, since the team itself is part of a key (a ‘prime attribute’<\/strong><\/a> in modeling terms.) So what exactly is the problem with this form, and why would we want to move past it to BCNF? <\/p>\n\n\n\n

Well, the example table has the dreaded delete anomaly<\/strong>. If employee “Schrute, D.” drops from the team “Office Conflict Resolution”, the fact that Smith is a mentor for this team is lost. <\/p>\n\n\n\n

Decomposing the table into two BCNF-compliant tables solves that problem.<\/p>\n\n\n\n

Can every table always<\/em> reach BCNF? (No – here’s the exception)<\/h2>\n\n\n\n

A table can always be structured to at least the 3NF level, but a table in 3NF can’t always<\/em> be converted to BCNF without losing a functional dependency. <\/p>\n\n\n\n

Here’s an example hospital shift roster of nurses assigned to patients:<\/p>\n\n\n\n

\"image<\/figure>\n\n\n\n

Each nurse can have multiple patients, but is assigned to one shift only. Each patient has a single nurse for each shift. In modeling terms, these two functional dependencies are represented as:<\/p>\n\n\n\n

nurse<\/code>\u00a0\u00a0 >>> \u00a0 shift<\/code><\/p>\n\n\n\n

(patient, shift)<\/code> >>>\u00a0\u00a0 nurse<\/code><\/p>\n\n\n\n

The possible candidate keys<\/a><\/strong> are (patient, nurse)<\/code> and (patient, shift)<\/code>. See the overlap? <\/p>\n\n\n\n

This is the only time you\u2019ll see a table in 3NF but not<\/em> in BCNF.<\/strong><\/p>\n\n\n\n

Why is this table in 3NF but not<\/em> in BCNF?<\/h2>\n\n\n\n

This table isn’t BCNF because the shift<\/code> column depends on nurse<\/code>. If you know the value of nurse<\/code>, you also know their shift – but nurse<\/code> is not<\/em> a possible key for the table. <\/p>\n\n\n\n

3NF, however, does allows this, because shift<\/code> is part of the (patient, shift)<\/code> key – a prime attribute.<\/p>\n\n\n\n

\n
\n\n

You may also be interested in…<\/strong><\/p>\n\n\n\n

Primary Key Primer for SQL Server<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

Now, watch what happens when we decompose this 3NF table into two separate<\/em> BCNF-compliant tables:<\/strong><\/p>\n\n\n\n

\"an<\/figure>\n\n\n\n

Now, the restriction that a patient can only have one nurse per shift is lost. <\/p>\n\n\n\n

You’ll notice I’ve changed the same data slightly, so that patient “Mallon, Mary” now has two nurses assigned to the same shift – something that wasn’t possible in the original table.<\/p>\n\n\n\n

There are limitations – you can only recover and enforce the lost constraint by joining the two tables, for example.<\/p>\n\n\n\n

Still, though, that’s better better than the redundancy<\/strong> and delete<\/strong> anomaly caused by leaving the table at 3NF. <\/p>\n\n\n\n

To see this, in the original 3NF table, let’s assign a nurse assigned to three different patients:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If Nurse Barton changes shifts, three rows will require editing, not just one. This violates the ‘single source of truth’ rule as a nurse’s shift should only<\/em> be stored in one location. <\/p>\n\n\n\n

And, if a nurse has no patients, there’s no way to represent what shift they’re assigned to in the first place.<\/p>\n\n\n\n

What’s next: ‘Fourth Normal Form’ and ‘Fifth Normal Form’<\/h2>\n\n\n\n

The last two rules are ‘Fourth Normal Form’<\/strong> and ‘Fifth Normal Form’<\/strong>. <\/p>\n\n\n\n

These are rarely taught in college data modeling classes, which is a shame because the situations they address crop up quite often in real-world databases. <\/p>\n\n\n\n

We’ll cover their use in Part 3 of the series.<\/p>\n\n\n\n

Have any thoughts or comments? Feel free to share them down below!<\/em><\/strong><\/p>\n\n\n\n

\n
\n
\n
\n

Simple Talk is brought to you by Redgate Software<\/h3>\n
\n Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI. <\/div>\n <\/div>\n Discover how Redgate can help you<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n
\n

FAQs<\/h2>\n\n

1. What's the difference between 2NF and BCNF?<\/h3>\n
\n

2NF requires every non-key column to depend on the whole key, not just part of it \u2014 this only matters when a table has a multi-column key. BCNF goes further, requiring that columns depend on nothing except the key at all. Most tables that meet 2NF also meet BCNF, unless a column depends on part of another candidate key.<\/p>\n <\/div>\n

2. Is BCNF the same as third normal form (3NF)?<\/h3>\n
\n

Not quite. BCNF is a stricter version of 3NF. They behave identically except when a table has overlapping composite keys \u2014 3NF allows a column to skip depending on the full key if that column is itself part of another key, while BCNF doesn’t allow this exception at all.<\/p>\n <\/div>\n

3. Can every table be converted to BCNF?<\/h3>\n
\n

No. A table can always reach 3NF, but converting some 3NF tables to BCNF loses a functional dependency \u2014 meaning a business rule (like “one nurse per patient per shift”) can only be enforced by joining tables back together, not within a single table.<\/p>\n <\/div>\n

4. What is an update anomaly in database design?<\/h3>\n
\n

An update anomaly happens when the same fact is duplicated across multiple rows, so changing that fact means editing several rows instead of one. This risks rows disagreeing with each other and breaks the “single source of truth” principle that normalization is designed to protect.<\/p>\n <\/div>\n

5. What does "the key, the whole key, and nothing but the key" mean?<\/h3>\n
\n

It’s a mnemonic for three normalization rules: every column must depend on the table’s key (1NF\/2NF basics), on the entire<\/em> key when the key has multiple parts (2NF), and on nothing but<\/em> the key (BCNF). The full phrase adds “so help me Codd,” honoring Dr. Edgar Codd, who created the rules.<\/p>\n <\/div>\n <\/section>\n","protected":false},"excerpt":{"rendered":"

Confused by 2NF and BCNF? Learn database normalization with real examples, so you can spot update and delete anomalies before they cause problems.…<\/p>\n","protected":false},"author":343663,"featured_media":103888,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53],"tags":[4168,4170,159415],"coauthors":[159110],"class_list":["post-112978","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","tag-database","tag-database-administration","tag-normal-forms"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112978","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/343663"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=112978"}],"version-history":[{"count":21,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112978\/revisions"}],"predecessor-version":[{"id":113451,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112978\/revisions\/113451"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/103888"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=112978"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=112978"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=112978"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=112978"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

This rule is formally known as Boyce-Codd Normal Form (BCNF)<\/a><\/strong>. The latter name refers to Dr. Edgar Codd<\/a>, who first fabricated these normalization rules and is immortalized in the full version of our mnemonic phrase: “the key, the whole key, and nothing but the key — so help me Codd”.<\/p>\n\n\n\n