Creating a FK when column lengths are not the same

• zake

  SSC Enthusiast

  Points: 158

  More actions

  September 5, 2006 at 7:16 am

  #367308

  Hi,

  I am trying to create a FK on a column that references another column that is not the same length.  We are trying to generate diagrams for customers to be able to create reports.  These keys are not going into a production database, but a database without any data that will be used to generate diagrams.

  Here is the example

  table_1(user_nm varchar(16))

  table_2(log_number int, user varchar(25))

   

  I am trying to create a FK on Table 2 that references the user_nm column in table 1, but it is failing with the following error:

  Server: Msg 1753, Level 16, State 1, Line 4

  Column 'dbo.table_1.user_nm' is not the same length as referencing column 'table_2.user' in foreign key 'FK_table2_user'.

  I understand the problem, but does anybody know how to create this relationship other than modifying the length in Table_2?

   

  Thank you,

  J. Arpide

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  September 5, 2006 at 8:00 am

  #658543

  Alter table 1 to make the length 25.

  Not sure why you want them to be different. Especially with varchar, just put the smaller one at the length of the larger one.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 5, 2006 at 8:00 am

  #658541

  If you can't alter one of the columns, then you have to enforce it with a trigger.