Can a field be made mandatory for just new records?

Hi,
Is there any way in SQL server 2000, of making a field mandatory in an
existing table (which already contains thousands of records), without
having to update all of the existing records?
In otherwords, can a field be made mandatory for just new records?
If not, is the only solution to code it into my front end application?
Thanks
ColinThis doesn't really make much sense to me, so far. What is the point of
making the column mandatory if you're not going to update the existing rows?
Is there some application limitation that requires a value? If so, why
would the limitation only be relevant on new rows? Can the application not
look at old rows? Why is it okay for an old row to be NULL and not for a
new row? Just trying to understand the logistics.
If you want only new rows to contain a value, then it is fairly trivial to
have your insert stored procedure (you are using stored procedures, right?)
make that parameter NOT optional, and return an error if it is NULL. (You
will probably want to slightly change your form appearance and/or
validation.) But you're not going to be able to enforce this at the table
level, as far as I can tell (but maybe if you detail your reasoning it may
spawn additional thought).
A
"Bobby" <bobby2@.blueyonder.co.uk> wrote in message
news:1189685999.863746.93220@.g4g2000hsf.googlegroups.com...
> Hi,
> Is there any way in SQL server 2000, of making a field mandatory in an
> existing table (which already contains thousands of records), without
> having to update all of the existing records?
> In otherwords, can a field be made mandatory for just new records?
> If not, is the only solution to code it into my front end application?
> Thanks
> Colin
>|||On 13 Sep, 13:31, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> This doesn't really make much sense to me, so far. What is the point of
> making the column mandatory if you're not going to update the existing rows?
> Is there some application limitation that requires a value? If so, why
> would the limitation only be relevant on new rows? Can the application not
> look at old rows? Why is it okay for an old row to be NULL and not for a
> new row? Just trying to understand the logistics.
>
My FE application is written in Access 2003. I have a form which I use
to create Purchase Orders. This form has a sub form for PO Items. Due
to a change in company procedures, I need to add four fields to the
sub form which all require user input. However, this only applies to
new POs. There is no sense in going back through five years worth of
(20,000) existing POs to make sure that all four columns conform and
have the correct data.
> If you want only new rows to contain a value, then it is fairly trivial to
> have your insert stored procedure (you are using stored procedures, right?)
> make that parameter NOT optional, and return an error if it is NULL. (You
> will probably want to slightly change your form appearance and/or
> validation.) But you're not going to be able to enforce this at the table
> level, as far as I can tell (but maybe if you detail your reasoning it may
> spawn additional thought).
>
I'm not using a stored procedure on this form, but perhaps that's the
answer.
Thanks for your help
Colin|||> new POs. There is no sense in going back through five years worth of
> (20,000) existing POs to make sure that all four columns conform and
> have the correct data.
No, but you could update them all in one shot with some token value (e.g.
N/A) and then you could apply your constraint and prevent further rows from
being un-populated.
A|||<snip>
> to a change in company procedures, I need to add four fields to the
> sub form which all require user input. However, this only applies to
> new POs. There is no sense in going back through five years worth of
> (20,000) existing POs to make sure that all four columns conform and
> have the correct data.
Before you go further, why don't you step through the process of what is
expected when someone modifies a PO created before your change (regardless
of how it is accomplished). Will your front-end somehow "know" that the PO
was created before the requirement and will correctly "adjust" its
appearance and logic to account for this not-present and not-required data?
If you have difficulty answering that question, then you are in a bit of a
cart-before-horse situation since you need to define the business logic
first.
There is an alternative that will support your stated goal. Create a
dependent table (in a 1-0/1) relationship that contains your new columns.
Your existing rows will have no associated row in this new table, while any
orders created (and, perhaps, modified) after this change will (or at least
can) have a row.|||On Sep 13, 7:19 am, Bobby <bob...@.blueyonder.co.uk> wrote:
> Hi,
> Is there any way in SQL server 2000, of making a field mandatory in an
> existing table (which already contains thousands of records), without
> having to update all of the existing records?
> In otherwords, can a field be made mandatory for just new records?
> If not, is the only solution to code it into my front end application?
> Thanks
> Colin
I agree with Aaron - you most likely don't want do it. Yet it is
doable:
CREATE TABLE a(i INT)
INSERT a(i) VALUES(NULL)
GO
ALTER TABLE a WITH NOCHECK ADD CONSTRAINT a_i_notnull CHECK(i IS NOT
NULL)
-- creates OK
GO
INSERT a(i) VALUES(NULL)
/*
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint
"a_i_notnull". The conflict occurred in database "FinancialDW", table
"dbo.a", column 'i'.
The statement has been terminated.
*/|||> I agree with Aaron - you most likely don't want do it. Yet it is
> doable:
Yes, of course. Why do I always forget NOCHECK? Probably because it's not
a very good practice for this kind of situation. :-)
A