1

Ms Access Table Screenshot

I am planning to move my access database to sql server using SSMA. I have a Column called Eligibility which have drop down list values as shown in Image. After Converting to sql I realized it doesn't have drop down list option. Can anybody suggest what will be the best solution of my situation? Either I can have any other option to design table in SQL which can hold List Values?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user3167881
  • 55
  • 1
  • 1
  • 4
  • I believe the following answer is what you need: http://stackoverflow.com/questions/2441427/restrict-varchar-column-to-specific-values – NickyvV Jan 20 '14 at 11:29

2 Answers2

2

SQL Server does not work the same as access. It does not have dropdown option for you to choose from.

The proper way to implement dropdown option with SQL Server as database is to have another application as a front-end and let user access through the application. That way it is easier to manage security.

vasin1987
  • 1,962
  • 20
  • 26
2

You can do one of the following:

  1. Add CHECK constraint to Eligibility field allowing only a set of predifined values to be inserted into that field, as suggested in comment.
  2. Better solution would be to create Eligiblity table (with id and value fields), and reference this table from main table by id field, possibly creating a PK-FK relationship. This way: a) Only values from Eligibility table would be allowed. b) You could change and add entries in Elibility table without need to change constraint every time. c) A frontend application could use Elibility table to add drop-down functionality.
AdamL
  • 12,421
  • 5
  • 50
  • 74
  • @@frikozodi, If the Values are Less than 10 and is fixed? Should i create separate look up table or apply Constraint ? – user3167881 Jan 20 '14 at 12:36
  • @user3167881 I think that lookup table is still better, even performance-wise alone. Instead of multiple text entries (avg~8 bytes) in main table, you can have tinyint id (1 byte). – AdamL Jan 20 '14 at 13:16