Setting not NULL constraint afterwards

Thread View: borland.public.interbase.sql

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by Mika Parjanen Thu, 12 Dec 2002 07:08

#11224

Author: Mika Parjanen
Date: Thu, 12 Dec 2002 07:08

16 lines
1305 bytes

Hi,

just a simple question:

How can I set a field NOT NULL constraint afterwards.
There is a lot of data in that table and too much depencies
so I cannot drop and recreate that field again.

I suppose "DEFAULT 0"  would also be enough.

This is a problem because I have triggers which
increment and decrement that value and that can't be done
if value is NULL.

Thanks for reading
Mika Parjanen

Re: Setting not NULL constraint afterwards

#11225

Author: Martijn Tonies
Date: Thu, 12 Dec 2002 08:01

35 lines
1843 bytes

Hi,

> just a simple question:
>
> How can I set a field NOT NULL constraint afterwards.
> There is a lot of data in that table and too much depencies
> so I cannot drop and recreate that field again.
>
> I suppose "DEFAULT 0"  would also be enough.

In IB6 or higher, it should be.

Also simply filling the column yourself,
UPDATE ... SET MYCOL = 0 WHERE MYCOL IS NULL

would do the same thing.

> This is a problem because I have triggers which
> increment and decrement that value and that can't be done
> if value is NULL.



--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

Re: Setting not NULL constraint afterwards

#11236

Author: Mika Parjanen
Date: Thu, 12 Dec 2002 14:22

72 lines
2682 bytes

The problem lies a little deeper since there are certain triggers and
other activities that cretes new lines to that table. I need to make sure
that there is never  a line with NULL value.

The table should be like this

CREATE TABLE MYMISTAKE (
   NR  INTEGER NOT NULL,
   PROBLEM INTEGER NOT NULL,
   .....
  )

or

CREATE TABLE MYMISTAKE (
   NR  INTEGER NOT NULL,
   PROBLEM INTEGER DEFAULT 0,
   .....
  )

but now it is

CREATE TABLE MYMISTAKE (
   NR  INTEGER NOT NULL,
   PROBLEM INTEGER,
   .....
  )

so I should be able to add that constraint which make that value = 0
if the insert don't set that PROBLEM field

Mika Parjanen

"Martijn Tonies" <m.tonies@upscene_remove.com> kirjoitti viestiss�
news:3df841c4@newsgroups.borland.com...
> Hi,
>
> > just a simple question:
> >
> > How can I set a field NOT NULL constraint afterwards.
> > There is a lot of data in that table and too much depencies
> > so I cannot drop and recreate that field again.
> >
> > I suppose "DEFAULT 0"  would also be enough.
>
> In IB6 or higher, it should be.
>
> Also simply filling the column yourself,
> UPDATE ... SET MYCOL = 0 WHERE MYCOL IS NULL
>
> would do the same thing.
>
> > This is a problem because I have triggers which
> > increment and decrement that value and that can't be done
> > if value is NULL.
>
>
>
> --
>
> With regards,
>
> Martijn Tonies
> InterBase Workbench - the developer tool for InterBase & Firebird
> Firebird Workbench - the developer tool for Firebird
> Upscene Productions
> http://www.upscene.com
>
> "This is an object-oriented system.
> If we change anything, the users object."
>
>

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads