>Hi,
>
>Say I want to create a Foreign key constraint on two fields (one char and one smallint). In the parent table (where the combination of these two fields is unique) I will have a unique index on Char_field + SmallInt_fld.
>
>Do I need to have an index in the Child table (where the Foreign key constraint will be set) on these two fields (Char_field + SmallInt_fld) or two index keys, one for each field?
>
>The foreign key constrain will look something like this:
>
>
>ALTER TABLE [dbo].[child_table] WITH CHECK ADD CONSTRAINT [FK_child_somename]
>FOREIGN KEY([char_fld], [smallint_fld])
>REFERENCES [dbo].[parent_table] ([char_fld], [smallint_fld])
>ON UPDATE CASCADE
>
>
>TIA.

It should be one index per both fields. I suggest to make integer field first key and char the second in the list of fields.
This is assuming you always use both fields in join conditions and select filters.

If it's not broken, fix it until it is.


My Blog