Adding a foreign key to a table
Primary Keys and Foreign Keys in Access One of the first concepts that new users of Access have to wrap their head around is that of keys. A key is a way of connecting one record in a database with another record. Just like you have a different key for your car, your house and your office, each record will have a different key. The foreign key in MS Access is the same as in any other SQL server: a field (Column) in a table (the slave table) that you instruct Access (your data base management system) to consider to be compulsory linked to a field in another table (the master table) so that ACCESS will not allow anyone to enter a record (row) into your slave table with a value in that field that does not have a record in the .
One of the first concepts that new users of Access have to wrap their head around is that of keys. A key is a way of connecting one record in a database with another record.
Just like you have a different key for your car, your house and your office, each record will have a different key. Having the key gives you access to the record.
Simple, right? Each record in a database is usually going to need a Primary Key. The Primary Key for each record has to be unique inside that table of the database, and is oftentimes a number. For example, a list of customers might have a field called customerNumber. Each record has a unique customerNumber, so we can safely use it as a Primary Key. Remember, the most important aspect of a Primary Key is that it has to be unique inside the table.
That also means that Primary Keys never get re-used. Another advantage of using keys in a database is that it allows us to reduce redundancy of information. In our example, the table of invoices has a Primary Key field called invoiceNumber, but it needs some way to link to the table of customers so that you can mail invoices to the correct customer.
When a Primary Key appears in another table, we call what does it mean if you have clammy hands a Foreign Key. Foreign Keys do not have to be unique. That is a Best Practice for humans, but not required by the computer.
Techopedia Explains Foreign Key
The Foreign Key is essentially the Primary Key from one table placed in another table in order to join them. When creating a report to see how many sales there are per customer, access will look at a sales record, see that there is a Foreign Key and look in the relating customer table at the matching number to obtain the customer details/5(K). The FOREIGN KEY constraint is used to link records of one table to the records of another. When you define a FOREIGN KEY constraint on a column, a column with the same name must exist as a primary key in another table. A foreign key (sometimes called a referencing key) is a key used to link two tables together. Typically you take the primary key field from one table and insert it into the other table where it becomes a foreign key (it remains a primary key in the original table). More complicated but fuller explanation.
All the how-to stuff I've read so far explains that I can create relationships between tables by adding a foreign key. Do I just type the name of the key in the "many" table?
Nope, that doesn't work. Is there a way to do it in the Relationship views? Maybe, but I don't see how. Is adding a foreign key that obvious and am I that stupid??? I need more control over the mechanics of my design. TitleID field. This should create a basic relationship. You can then double-click on the relationship link the connecting line and it will open a dialog in which you can enforce referential integrity, push updates, push deletions.
Was this reply helpful? Yes No. Sorry this didn't help. Thanks for your feedback. A Foreign Key is defined not by how the field is created , but by how it is used. A field becomes a Foreign Key when you use the Relationships Window to create a link between two tables, connecting the Primary Key of one table the "one" side table to a field of the same datatype and size in a second table the "many" side table and checking the "Enforce Referential Integrity" button.
Often but not required the Primary Key will be an Autonumber field; if so, you would need to have a field of Long Integer datatype in the child table. Different folks use different naming conventions; Access doesn't care what the fieldnames are, but you should have a standard naming convention that makes sense to you.
The Lookup Wizard also creates relationships, but most of us would agree with you that it should be avoided. It's very limited and can cause as much confusion as benefit. Fields are added in Table Design view. You go to a blank line, type in the name you want for the field and then select a datatype. The datatype has to match the type of the Primary key it will be related to. The name you assign will usually be the same as the PK it will be related to,. As John said a FK is defined by usage, nothing else.
So if you add a field to a table that is intended to store the PK value for the related record in the related table then you have a foreign key. Nothing more is necessary. It is however, a good idea to formalize your relationships in the Relationships window so that you can impose Referential Integrity.
So you DO "just type the name of the key in the "many" table". I suspect however you were trying to do this in Datasheet view instead of Table Design mode. I do not recommend adding fields that way.
It does not give you any control over the properties of the field you are adding. How are these column values set? What is the mechanical process as you ask! How do you set this column value to relate back to the parent table? Or you can write some code to do this for you! In other words, when you add a record in a form, you THEN must type in the value for this column used to relate back to the parent record.
Or as noted, have soe code set this value. Another way recommended way is to build a form with a sub form. When you use a sub-form, then for each child record you add, the FK foreign key value can be will be set for you automatically by Access. And if you have a main form, and you have say a button on the form to launch a form based on the child table, then you will have to PASS the primary key value from that form.
Or have the form look back to the other form. So YOU will write code in the form based on this table of child records to correctly SET the FK value column to relate back to the one parent record in the parent table. So the FK column is in general a plane Jane long number type of column.
This long number column thus must have a value set that points back to the parent record the value you enter into this editable columns is the PK auto number ID of the parent record you wish to relate to.
And MOST important is then your code, your form etc. MUST set this value to attach the record to the parent record. In other words, either your code or your user will have to choose and type in this value. Once you set this, then for each child record you add, the parent record pk ID value will be pulled and placed into the FK. If the two forms are separate, then in the second forms on-insert event, you can set the FK id in code like this. Of course the above is for child records. For tables that drive a user set of choices, then a combo box is most often how users select such values.
First of all, thank you to all of you who've responded to my question. I have to admit that a couple of the answers, while quite intelligent, strained my brain. One thing I did understand is that I can create FK relationships in the Relationships window by dragging one key to another table. But I'm finding that Access won't always allow me to drag a key in the direction I want to drag it.
I get the crossed out circle symbol. Bottom line: I think my design logic is wrong. Here's what I want to do: I have list of philanthropic foundations that, among them, provide a variety of conservation grants. Then I have a variety of conservation projects that my nonprofit group seeks funding for. What I want to do is match up our project needs with the appropriate grantors.
Here's a concrete example: We have project types 1, 2, 3 and 4. Grantor A will fund project types 1 and 2. Grantor B will fund project types 1, 3 and 4. Grantor C will fund project type 2. And so on. So in the user interface Form I'm building I need to show this information. Note that any given grantor could have anywhere from one to four project types that match our needs. There would never be zero project types, as that grantor simply would not be listed.
I've solved this problem so far -- in a way that I know is bad design -- by simply adding a ProjectType1 field, ProjectType2 field, etc, in the Grantor Table, each of which looks up a value in a Project Types Table that I've created. I used the Lookup Wizard to create the look-up relationships. You'll find the file as StudentCourses. Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link copy its text NB, not the link location and paste it into your browser's address bar.
What this little demo file illustrates is a many-to-many relationship type between students and courses. A many-to-many relationship type is modelled by a third table which resolves it into two or more one-to-may relationship types.
You have a many-to-many relationship type between grantors and project types, so you would model it by a table which has two foreign keys, GrantorID and ProjectTypeID say which references the primary keys of Grantors and ProjectTypes respectively. The primary key of this table is a composite one made up of the two foreign keys columns.
This type of table is sometimes colloquially referred to as a 'junction' table. A project types form for instance would have a subform based on the table which models the many-to-many relationship type. Alternatively the parent form could be a grantors for, depending on whether you want to enter grantors per project type, or vice versa. Thanks for your help. Actually, thanks to everyone. I think I finally figured out the junction table join.
I've attached a picture of that. But there's still a disconnect between the relationships and the values they represent. That is, I have no idea how to populate the junction table, not with the keys, but with the values the keys represent.
So I've attached two more pictures as well: One is my attempt at a subform; you can see it is only showing a number, not an actual grant type description. But in actual fact, what I really need is not a subform, but a multi-selection list.
So I've also attached a picture of one of the decision points in the list box Wizard, since I'm not sure what I'm supposed to select. Tables are kept "under the hood": they typically WILL have numeric links to other tables, not the actual text values. That's fine; you'll only look at the contents of your tables while you're developing or debugging the database, and your end users should never see them at all!
Instead, all interaction with your data should be done using Forms usually with Subforms and other kinds of controls. This will let you select as many rows of grant types as you like. You have them reversed. It's doable if you really need that user interface but in practice subforms are very user friendly. Second, I agree with John, that, while a multi-select listbox might be a little easier for the user, its a lot hard for the developer. Using a Continuous form subform makes it easier to record, display and modify the list if grant types for a Grantor, You could also have another form where Grant-Type is the source of the main form and the subform displays the grantors associated with that grant type.
So you can enter the data either way.