1 / 16

Outline

Outline. What is a Primary Key? AutoNumber primary keys Single-field primary keys Composite-field primary key About Foreign Keys Database Relationships A one-to-many relationship A one-to-one relationship A many-to-many relationship About defining relationships. What is a Primary Key?.

ramona
Download Presentation

Outline

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Outline • What is a Primary Key? • AutoNumber primary keys • Single-field primary keys • Composite-field primary key • About Foreign Keys • Database Relationships • A one-to-many relationship • A one-to-one relationship • A many-to-many relationship • About defining relationships Ms D. Anderson

  2. What is a Primary Key? • The power of a relational database system such as Microsoft Access comes from its ability to quickly find and bring together information stored in separate tables using queries, forms, and reports. In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, Access will prevent any duplicate or Null (empty) values from being entered in the primary key fields. Ms D. Anderson

  3. AutoNumber primary keys • An AutoNumber field can be set to automatically enter a sequential number as each record is added to the table. Designating such a field as the primary key for a table is the simplest way to create a primary key. If you don't set a primary key before saving a newly created table, Microsoft Access will ask if you want it to create a primary key for you. If you answer Yes, Microsoft Access will create an AutoNumber primary key. Ms D. Anderson

  4. Single-field primary keys • If you have a field that contains unique values such as ID numbers or part numbers, you can designate that field as the primary key. You can specify a primary key for a field that already contains data as long as that field does not contain duplicate values or Null values. Ms D. Anderson

  5. Composite-field primary key • In situations where you can't guarantee the uniqueness of any single field, you may be able to designate two or more fields as the primary key. Composite Primary Key Ms D. Anderson

  6. About Foreign Keys • A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables. The referenced table is called the parent table while the table with the foreign key is called the child table.  • Primary Key – PK • Foreign Key – FK Ms D. Anderson

  7. Database Relationships Ms D. Anderson

  8. A one-to-many relationship • A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A. • For example one supplier can supply more than one product to a company, but each product has only one supplier. Ms D. Anderson

  9. The Supplier - Product Relationship 1 ∞ PK FK Parent Table Child Table Ms D. Anderson

  10. A one-to-one relationship • In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. Ms D. Anderson

  11. Student Names - Details relationship 1 1 PK PK Ms D. Anderson

  12. A many-to-many relationship • In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A. This type of relationship is only possible by defining a third table (called a junction or join table) whose primary key consists of two fields — the foreign keys from both Tables A and B. A many-to-many relationship is really two one-to-many relationships with a third table. Ms D. Anderson

  13. For example, the Orders table and the Products table have a many-to-many relationship that's defined by creating two one-to-many relationships to the Order Details table. One order can have many products, and each product can appear on many orders. Ms D. Anderson

  14. Orders – Order Details – Products Relationship 1 PK ∞ 1 FK FK PK ∞ Composite Primary Key Ms D. Anderson

  15. About defining relationships • The kind of relationship that Microsoft Access creates depends on how the related fields are defined: • A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index. • A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes. • A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields — the foreign keys from the two other tables. Ms D. Anderson

  16. Outline • What is a Primary Key? • AutoNumber primary keys • Single-field primary keys • Composite-field primary key • About Foreign Keys • Database Relationships • A one-to-many relationship • A one-to-one relationship • A many-to-many relationship • About defining relationships Ms D. Anderson

More Related