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 |
|
BlueBell
Starting Member
4 Posts |
 Posted - 2013-12-13 : 11:07:49
|
| I have upsized an Access Database into SQLExpress and now need to set up some required fields. In the Access database these were originally set as Required at table level. I have tried setting the SQL table properties to NOT NULL but because the some records have no data in these fields I cannot use this method on all the fields. I have tried setting validation at form level but the validation rule and error message are not recognised.Two questions: How can I apply an error message to the NOT NULL SQL fields?How can I make a field mandatory for those fields which I cannot set in SQL? (or does the user have to tidy up the data?)I have new to SQL so attempting a steep learning cliff!Many thanks for any help |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-13 : 11:42:47
|
The "right" way to make a column mandatory is to set it to NOT NULL. If there are already nulls in the data then I would think those need to be set from a business standpoint (otherwise why is it mandatory?).Business requirements can be enforced in any architectural layer. Often they are enforced in the front end application so that the violations in can be handled quickly with friendly messages before anything is sent to the database. But a good DBA will tell you that database integrity needs to be enforced in the database - things like referential integrity and unique constraints and not allowing NULLs where there must be values. That way any access to the database is ensured to follow the rules - not just users of that specific application.EDIT:>>How can I apply an error message to the NOT NULL SQL fields?if you set the column to NOT NULL then sql will generate a standard applicable message for an attempt to violate that rule. The message will include enough details so that you know which column. It is up to the application to catch that error and display something different if you want something different displayed to the user.Here is an example of what sql will return:Msg 515, Level 16, State 2, Line 2Cannot insert the value NULL into column '<myColumn>', table '<myTable>'; column does not allow nulls. INSERT fails.The statement has been terminated. Be One with the OptimizerTG |
 |
|
|
d3rta
Starting Member
1 Post |
Posted - 2013-12-13 : 13:07:13
|
| making it a not null field is probably the best wayyou could also set a constraint if it needs to be more specific than just not null, depending on the program it may still allow blank entriesmake sure you UPDATE table set field='error cannot be null' where field=null first--thats what i just said |
|
|
|
BlueBell
Starting Member
4 Posts |
Posted - 2013-12-18 : 08:00:26
|
| Thank you both for your answers, which I agree with entirely. Unfortunately the data is not mine I am just upsizing the database and have no influence over the data. The customer did not want anything done to the data just it put into sql express to speed the database up. It appears to be a "homemade database" which has grown as the user has acquired a bit more knowledge hence the original fields have no data in and then the mandatory element must have been added later.Where you mention it is up to the application to catch the error and display the message, I was trying that with validation on the form - do you mean I need to program an error message in VBAMany thanks |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-18 : 10:14:46
|
On the application side there are two types of things you could/should do. Catch and handle any database errors so the user can see meaningful messages. And validate entry forms prior to submitting to the database to catch anything you know will fail or is not allowed. If your front end is Access then I suppose that means VBA but I'm not really sure - that's not my area of expertise.Here is one thing you can do on the database side that will prevent new null values but still allow the existing nulls to exist. add a table constraint with NOCHECK.alter table <yourTable> WITH NOCHECK add constraint <yourConstraintName> check (<yourColumn> is not null) There is a caveat though for using WITH NOCHECK. From Books Online:quote:
WITH CHECK | WITH NOCHECKSpecifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. This is not recommended except in rare cases. The new constraint will be evaluated in all future updates. Any constraint violations suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.Constraints defined WITH NOCHECK are not considered by the query optimizer. These constraints are ignored until all such constraints are re-enabled using ALTER TABLE table CHECK CONSTRAINT ALL.
Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Set a Mandatory Field