Database – Relational Model – Candidate Keys

 

Prelude

This is the first of hopefully a few postings on database modeling concepts.  In this post we will discuss Candidate Keys.

 

Background

Let us create a couple of tables.  A customer table and an email subscription table.

 

set noexec off
go

use master
go

if db_id('DBLab') is null
begin

    print 'create database DBLAB ...'

        exec ('create database [DBLAB]')
    
    print 'created database DBLAB'
end
go


use [DBLab]
go

if schema_id('commerce') is null
begin

    print 'create Schema commerce ...'

        exec ('create schema [commerce]')
    
    print 'created schema commerce'
end
go




if object_id('[commerce].[customer]') is null
begin

    create table [commerce].[customer]
    (

          [id]              bigint not null 
                              identity(1,1)
        , [customerName]    nvarchar(100) not null

        , [emailAddress]    nvarchar(200) not null

        , constraint PK_Commerce_Customer
            primary key
            (
              [id]
            )
    )


end
go


if object_id('[commerce].[emailSubscription]') is null
begin

    create table [commerce].[emailSubscription]
    (

        [emailAddress]    nvarchar(200) not null
                
      , [promotionEmail]  bit not null
          constraint constraintDefaultPromotionEmail 
            default (0)

      , [3rdPartyEmail]   bit not null
          constraint constraintDefault3rdPartyEmail 
            default (0)

      , constraint PK_Commerce_EmailSubscription
          primary key
             (
                [emailAddress]
             )

    )


end
go

 

Diagram

 

DataModel - Initial

 

 

Problem

We can not see any relationship between the two entities.

 

Address

 

Create Foreign Keys

Let us address by creating foreign keys ..

 

alter table [commerce].[emailSubscription]
    add constraint FK_EmailSubscription_Customer
        foreign key
            (
                [emailAddress]
            )
        references [commerce].[customer]
            (
                [emailAddress]
            )
go

 

We quick get a very helpful error from SQL Server…


Msg 1776, Level 16, State 0, Line 90
There are no primary or candidate keys in the referenced table 'commerce.customer' that match the referencing column list in the foreign key 'FK_EmailSubscription_Customer'.
Msg 1750, Level 16, State 0, Line 90
Could not create constraint or index. See previous errors.


 

Create Candidate Keys on Referenced Table

Let us go create a candidate key on our Reference Table (commerce.customer)

/*
    Create Constraint CONSTRAINT_COMMERCE_CUSTOMER_UNIQUE_EmailAddress
*/
alter table [commerce].[customer]
    add constraint CONSTRAINT_COMMERCE_CUSTOMER_UNIQUE_EmailAddress
        unique
            (
                [emailAddress]
            )
go

 

 

Retry Foreign Key Creation Step

 

/*
    Create Foreign Keys
*/
alter table [commerce].[emailSubscription]
    add constraint FK_EmailSubscription_Customer
        foreign key
            (
                [emailAddress]
            )
        references [commerce].[customer]
            (
                [emailAddress]
            )
go

 

No more errors…

 

 

Diagram

Let us recreate our Database Diagram

 

DataModel - ForeignKey

 

Candidate Keys – What good?

  • Better understanding of the entities and their relationship
  • SQL Queries are easier to write as one is better able to know which columns to include when joining tables
  • As artificial keys proliferate, it blindsides database records uniqueness understanding

Conclusion

Candidate keys are themselves fairly straight forward concepts.

But, we do get bogged down sometimes within our tools.

  • For example, one is unable to visually see Foreign Key Relationships when we target SQL Server Express Engine

 

References

 

How to

 

Q/A

 

One thought on “Database – Relational Model – Candidate Keys

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s