FOREIGN KEY CONSTRAINT:
Foreign key constraints define relationships among tables. Typically these are know as "parent_child" or "master_detail" relationships. There are two ways you can define foreign key constraint, either by using SQL code or using the Enterprise Manager in SQL Server. We are going to use the latter in this case. Consider a table "mrm_patient" with five fields and one primary key (sysno_patient)
mrm_patient
| sysno_patient(PK) | Last_ Name | First_Name | Address | Zip_Code |
| 146498 | Mitchel | John | 1223 West Palm Beach Rd | 85023 |
| 146499 | Spencer | Teri | 2349 S. 76th Street #102 | 53219 |
| 146500 | James | Taylor | 23 N. Atlantic Blvd | 76215 |
| 146501 | Pewinsky | Lewis | 675 E Indian School Rd | 85023 |
| 146502 | James | Sue | 3567 E Tatum Blvd | 85032 |
There is another table "mrm_condensed_history" with five field and one primary key (sysno_porder)
mrm_condensed_history
| sysno_porder(PK) | sysno_patient(FK) | exam_status_ind | exam_date | sysno_trans |
| 107425 | 146494 | 7 | Dec 7 1998 13:15PM | 7 |
| 107426 | 168314 | 5 | Dec 8 1998 11:30PM | 3 |
| 107427 | 135264 | 6 | Jan 21 1998 03:45PM | 8 |
| 107428 | 146502 | 8 | Feb 14 1998 01:00PM | 9 |
| 107428 | 146500 | 7 | Mar 7 1998 12:15PM | 2 |
Notice that both the above tables share a common field, the "sysno_patient" field. This field is used to identify the patient. The difference is that this is the primary key in table "mrm_patient" which is considered the parent table. Since table "mrm_condensed_history" already has a primary key "sysno_porder" we can assign a foreign key to "sysno_patient" and this table is considered the child table. Since these two tables have a common field, they have a relationship with each other.
In order for this relationship to exist, go to the Enterprise Manager and edit the child table "mrm_condensed_history". Click on the advance features and then click Foreign Keys. Now you can give a name to the foreign key let us say "FK_sysno_patient". Under the Referenced Table, click on dbo.mrm_patient which gives "sysno_patient" in Key Columns. Under the Foreign Key Columns, select "sysno_patient" and finally click the Add button. Now we have defined a foreign key and established a relationship with table "mrm_patient"
In order to have a meaningful relationship, we have to do one more thing. We have to make sure that there are no extra values in the child table that do not exist in the parent table. Once you click the Add button in the previous step, you want to save the table definition. If you get an error 549, that would occur due to extra values. These values should be located using an OUTER JOIN and eliminated before establsihing a relationship.