Database Fundamentals/Constraints

This lesson introduces constraints.

Objectives and Skills

Objectives and skills for the constraints portion of Microsoft Exam 98-364 Database Fundamentals include:^[1]

• Understand data storage
  • Understand primary, foreign, and composite keys
    • Understand the reason for keys in a database, choose appropriate primary keys, select appropriate data type for keys, select appropriate fields for composite keys, understand the relationship between foreign and primary keys

Readings

1. Read Wikipedia: Database constraint.

Multimedia

1. Watch Microsoft Virtual Academy: Relational Concepts.
2. Watch YouTube: Database Objects.

Activities

1. Use SQL Server to create constraints to# update record information in the Student table in your College database.
2. Create the following constraints:
   • Watch YouTube: Creating Primary and Foreign Keys in SQL Server 2012.
   • Read Microsoft: Create Primary Keys.
   • Read W3CSchools.com: SQL Primary Key Constraint.
   • Read SQL Foreign Key Constraint.
   • Create a constraint making the InstructorID of the Instructor table a foreign key in the Course table.
   • Create a constraint making the StudentID of the Student table a foreign key field in the StudentCourse table.
   • Create a constraint making the CourseID of the Course table a foreign key field in the StudentCourse table.
   • By using SQL Server, Object Explorer, use Database Diagram to create an E-R Diagram. Save the diagram with your database.

Lesson Summary

• A composite primary key containing at least one compound key with at least one other attribute or simple key (this is an extension of a compound key).^[2]
• A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.^[3]
• Foreign keys are defined in the ISO SQL Standard through a FOREIGN KEY constraint.^[4]
• The key that is selected as the primary key. Only one key within an entity is selected to be the primary key. This is the key that is allowed to migrate to other entities to define the relationships that exist among the entities. When the data model is instantiated into a physical database, it is the key that the system uses the most when accessing the table, or joining the tables together when selecting data.^[5]
• A unique key is a set of zero, one, or more attributes in database relational modeling and implementation.^[6]

Key Terms

composite primary key

foreign key

foreign key constraint

primary key

unique key constraint

References

Resource type: this resource is a lesson.