defining a dropdown list for a table field
-
Is it possible to define a dropdown list of predetermined field values in the table design? I can do this easily in
an application that does an update or insert into the database table but can this be designed within the table?
A constraint will make sure only certain values are valid for insert/update but in the table I want the user to pick
from a predetermined list of values? Thanks for any information as to how to do this or even if possible.
-
Wouldn't it be a charm if such thing is possible?
So to answer your question: No, you can't enumerate values for specific columns during table design. The closest thing you can do to validate input values to your column is by defining a foreign key constraint. -
This is perfect example for normalizing data...
You say you have data described "hard coded in your application as a drop-down list"?
Please create a "type" table for your column (example):
CREATE
TABLE dbo.MEASUREMENT_TYPE (
MT_CODE integer IDENTITY (1, 1) NOT NULL ,
MT_TYPE char(2) NOT NULL , /* user defined measurement type like 'FT', 'CM', etc.. */
MT_ORDER integer NULL default 0 ,
MT_SELECTED char(1) NULL default 'N' ,
MT_DESCRIPTION varchar(100) NULL ,
MT_TIMESTAMP datetime NULL DEFAULT getdate() ,
CONSTRAINT PK_MEASUREMENT_TYPE_1__10 PRIMARY KEY CLUSTERED (MT_CODE)
);
Grant all on dbo.measurement_type to public;
CREATE UNIQUE INDEX MEASUREMENT_TYPE_idx0 on dbo.MEASUREMENT_TYPE(MT_TYPE);
Create a constraint mapping your column to the type table (storing either the identity or char) within your field & join the tables for type descriptions, etc...
Then select from your type table when display your list of values are required such as in (drop-down)s, and when your users add new records to your type_table, then so does your application without recompile!!
Hope this helps...
Regards,Coach James
-
No, no, no...please leave the past (Access) behind you
. With that in mind, create an Access Data Project (ADP) and change it's data source to be your SQL Server. Now, that you've got the Tables Linked up, you MIGHT (because I haven't tried it) be able to work with them like you did with Access Databases before!However, it's a really bad design desicions to have "Pre-defined" magical numbers. Using an article from this website, I was able to create 2 tables for ALL of my lookup values in every table in my database! It's awesome!!!! No more Lookup tables but I still have the advantage of Foreign Keys!!!
-
Those who know little speak it the loudest!
If I felt someone was ready for combining all their type fields into one table, I would have suggested it.
But, please explain your thoughts on access, ADP, data sources, linked tables, bad design, magical numbers, and any other subject you’re currently learning about.
But, whatever you do, don't produce a ULR for the article you mention, which you say, is available via this site!
Regards,Coach James
-
I wasn't sure if your last sentence was sarcastic or not...but sure, I will not submit the URL...
Anyway, I tried the Access Data Project and it works great! I am not going to make this Post into a Document on how to make lookup lists in Access Tables. However, if you are familiar with Access, and you know how to do this (just go to the Table Properties and Click the "LookUp" Tab of the field you want to make a List on). You can start your ADP by going into Access: File --> New --> Project Using Existing Data... Then put the Connection info for your SQL Server. Viola! Nothing to it!
-
Yes, that would be preferred...
By the way, doesn't access create a type_table for you by using this process? Use access in design mode to see this, plus new code created for constraints, and also verify the resulting tables which are being created within your SQL database by looking up your schema changes based on DateTime.
You will see a new table for column type data to satisfy your (drop-down) requirements.
Regards,Coach James
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply