Search code examples
mysqldatabase-normalization

Second normal form : Artificial key vs Composite Primary Key

I recently read about the second normal form that: "If your table has an artificial primary key and no composite primary key, it's in second normal form". What if my table has an artificial key as well as a composite primary key in my table? Will it be in second normal form, then also?

Solution

  • A relation in which every candidate key consists of a single column is in 2NF. (The relational model always considers every candidate key.)

    element_name  atomic_number  chem_symbol
--
Hydrogen      1              H
Helium        2              He
Lithium       3              Li
...

    Each attribute here is a candidate key. This table is in 2NF. It's also in 3NF, BCNF, 4NF, and 5NF.

    What if my table has an artificial key as well as a composite primary key in my table? Will it be in second normal form, then also?

    It depends on the non-prime attributes and their dependencies. If you have a non-prime attribute that's functionally dependent on part of that composite primary key, then the relation is not in 2NF.