Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2004-09-17 : 15:33:26
|
| I'm guessing I'm not the first person to ask this, but my search here was not fruitful. I have a table with an IDENTITY column of type integer called IntegrationID. My co-developer has an Access form in which the IntegrationID is a selectable field, but most often not applicable, so he'd like to have a row with an IntegrationID of "N/A". The problem of course is that this is not a valid integer value. My solution to this is to create a new CHAR column and do a trigger to copy the integer value to it when a row is added, excluding the N/A row, but is there a better solution I'm not thinking of? |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-09-17 : 15:39:06
|
| I've never used access forms, but can you make it so when they select N/A it maps to a NULL value, this way you can have your foreign key column be an integer that accepts null values. |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-17 : 15:51:27
|
| Probably there's a way to do that, with VBA if nothing else, but my Access buddy doesn't know how, and neither do I. I'm confused though, how can a key column accept NULL values? |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-09-17 : 16:43:41
|
| Primary Key's cannot be null, but you can make foreign keys that accept null values. |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-09-17 : 20:47:21
|
| I guess you could modify the query to do:select convert(varchar,id) id, id, col1, col2...order by 2unionselect 'N/A',0,null,null,nullor create a view that does the same thing and have your access app select from the view.The order by 2 and the second instance of id and 0 are only there to allow N/A to be at the top of the select list if you want that.--KenI want to die in my sleep like my grandfather, not screaming in terror like his passengers. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-09-17 : 22:08:18
|
| Why don't you just treat -1 as N/A... The N/A is merely presentation, and a -1 would be easy to filter out in queries and what not...Corey |
|
|
|
|
|
|
|
|
N/A row in Identity column