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 |
|
Pace
Constraint Violating Yak Guru
264 Posts |
 Posted - 2004-08-06 : 06:09:09
|
Hi All! What can I say im still learning  This should be a breeze as your all the bomb at this  I have created a database for me to play with... I have the following fields in the table "users":UserNameLoginNamePassDepartmentI now want to be able to join another table "userAdditions" to this one with the followingidNameOtherInfoI want to create the "id" (primary key) field on the original "users" table so that I can join them up... however in EM when I try to add the field "id" to the "users" table I get the following error;'users' table- Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'id', table 'test.dbo.Tmp_users'; column does not allow nulls. INSERT fails.[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.I dont have allow nulls checked either... Anyhelp would be greatly appreciated!  |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-06 : 08:00:04
|
| I think it's probably down to the way EM creates new fields. Have you specified what your default value is?What sort of data type is ID?-------Moo. :) |
 |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-08-06 : 08:14:47
|
| I don't think ID's datatype is the issue. If you're trying to add a new column to a table that contains rows you must either specify a default (which is not what you want here) or allow NULLs in the column definition.What you need then do is update the existing data from your UserAdditions table and if you like you can then apply the not null property to the ID column.Raymond |
|
|
|
Pace
Constraint Violating Yak Guru
264 Posts |
Posted - 2004-08-06 : 08:57:21
|
| thanks, a great help |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-08-06 : 09:10:43
|
| you do not have to create "ID" columns for joins -- is there already a relationship between these two tables? Is the "Name" column the same as the UserName or LogonName column? if so, you can join based on that. Since you are just learning, the best advice i can give is to think in terms of how the DATA relates between tables, not how you can add ID's to tables so you can join them up.- Jeff |
|
|
|
Pace
Constraint Violating Yak Guru
264 Posts |
Posted - 2004-08-06 : 11:04:20
|
| cool thanks geoff ;) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-08-13 : 11:20:35
|
| if you plan to make ID as unique and don't have to worry updating the table just to populate the new field, use int and define a seed value |
|
|
|
|
|
|
Unable to modify table - really easy