Looking up constraints for a value

When inserting/updating a value in a column on which a Foreign Key CONSTRAINT is defined SQLyog GRID and FORM view (in Table data) provide you with a facility to look up values in the 'parent' table. When such a CONSTRAINT exists the cell where you enter data will display a '...' button to the right in GRID as well as the FORM view. In GRID view to indicate that the columns have the foreign key contraint, a icon of "a table with a key" is present next to it.

Foreign Key Constraint in GRID mode

Foreign Key Constraint in FORM mode

If you click this button SQLyog will find matches for what you have entered in the 'parent' table. You can also use Ctrl+Space from the cell to open the same. It does so in two steps:
1 First it will try to find an exact match (using " = " operator) in the 'parent' column of the 'parent' table.
2 If no match is found in first step it will try to find a 'fuzzy' match (using " LIKE " operator with wildcards attached to the string before and after) against all columns of the 'parent' table.
Note that both searches will be case insensitive when searching on strings except for columns stored with a binary or case sensitive collation. A new dialog with the result of this search is displayed. 

To explain the various options that the look up table offers let us take an example.

The parent table "language" consists of primary language, primary_language_identifier, sub_language and sub_language_identifier. The child table "country_language" consists of country_name, language_id and sub_language_id. Suppose while entering the data in the table "country_language" you know the name of the language spoken but not its corresponding id. Then enter 'English' for instance against the country name Canada (it does not matter what datatype is used for that column or whether 'English' is a valid entry here. It does not matter because 'English' will not be saved - it is a search string used internally) before clicking the '...' button and you will see all the rows that match the string 'English' displayed from the parent table. You can of-course change the search string in the filter field once the dialog box is displayed.

However searching in all columns of the 'parent' table may sometimes return too much 'noise' making it difficult to find the one value you require. To avoid this in the combo box you may select specific columns to be searched on and perform the search again and thus 'narrow' the result.

Once you have entered the language_id and move onto enter the sublanguage_id then, all the sub_language_id's matching the corresponding language_id will get displayed on clicking "…" button if "filter on values already entered" is checked. If the option is not checked then it displays all the values present in the parent table.

Since in the above screen shot "filter on values already entered" is unchecked it displays all the entries from the parent table.