This is the first of hopefully a few postings on database modeling concepts. In this post we will discuss Candidate Keys.
Let us create a couple of tables. A customer table and an email subscription table.
We can not see any relationship between the two entities.
Create Foreign Keys
Let us address by creating foreign keys ..
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)
Retry Foreign Key Creation Step
No more errors…
Let us recreate our Database Diagram
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
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
- Create Foreign Key Relationships
- SQL Server Management Studio Foreign key info is hard to find
- How to make Database Diagram visually show Foreign Keys in Management Studio?