Thread: SET NULL / SET NOT NULL
Hi guys, I've been chatting to Tom about implementing the ability to change the NULL status of a column via SQL. This is the Oracle syntax: alter table table_name modify column1 not null; alter table table_name modify column1 null; This is the MySQL syntax: ALTER TABLE asfd CHANGE [COLUMN] old_col_name create_definition [FIRST | AFTER column_name] or ALTER TABLE asfd MODIFY [COLUMN] create_definition [FIRST | AFTER column_name] CHANGE col_name, DROP col_name, and DROP INDEX are MySQL extensions to ANSI SQL92. MODIFY is an Oracle extension to ALTER TABLE. So, the question is - what the heck is the standard syntax? Is there a standard syntax? How about this syntax that I came up with: ALTER TABLE blah ALTER COLUMN col SET [NULL | NOT NULL] Anyone have any ideas? Perhaps we should use some sort of 'MODIFY'-like syntax to enable in the future maybe the ability to change column specs in more advanced ways (such as column type and size) If the answer is no, Postgres's parser does not have this syntax enabled, then I'm going to have to ask someone to implement it for me, and then I can fill in the actual guts of the function - whereever that may be. (I don't know parser stuff!) Chris
At 09:59 15/02/02 +0800, Christopher Kings-Lynne wrote: > >ALTER TABLE blah ALTER COLUMN col SET [NULL | NOT NULL] > I'm not too fond of 'SET NULL' - the syntax implies the column is being set to NULL. But I agree with the rest given we already have ALTER TABLE...ALTER COLUMN, I'd vote for: ALTER TABLE blah ALTER COLUMN col [ALLOW NULL | NOT NULL] ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes: > I'm not too fond of 'SET NULL' - the syntax implies the column is being set > to NULL. But I agree with the rest given we already have ALTER > TABLE...ALTER COLUMN, I'd vote for: > > ALTER TABLE blah ALTER COLUMN col [ALLOW NULL | NOT NULL] FWIW, I like this syntax too. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
> > I'm not too fond of 'SET NULL' - the syntax implies the column > is being set > > to NULL. But I agree with the rest given we already have ALTER > > TABLE...ALTER COLUMN, I'd vote for: > > > > ALTER TABLE blah ALTER COLUMN col [ALLOW NULL | NOT NULL] > > FWIW, I like this syntax too. Let's say, theoretically, that in the future we want to allow people to change the type of their columns, plus allow them to change the nullability. Should we come up with a syntax for changing nullability that allows for the future changing of column type? If so, then a syntaxes like these might be the way to go: ALTER TABLE blah ALTER COLUMN col DROP DEFAULT; ALTER TABLE blah ALTER COLUMN col SET DEFAULT 't'; ALTER TABLE blah ALTER COLUMN col NULL; ALTER TABLE blah ALTER COLUMN col NOT NULL; ALTER TABLE blah ALTER COLUMN col varchar(50); ALTER TABLE blah ALTER COLUMN col int4 NULL; ALTER TABLE blah ALTER COLUMN col text NOT NULL; If we just allow the full col spec we could one day support this: ALTER TABLE blah ALTER COLUMN col text boolean NOT NULL DEFAULT 'f'; Which would change the column to that definition (if coercion is possible) no matter what current definition is... Is this the eventual goal? Will this cause shift/reduce errors? will we need to put the word 'SET' in after 'col'? Chris
> > ALTER TABLE blah ALTER COLUMN col [ALLOW NULL | NOT NULL]
> FWIW, I like this syntax too.
What would be the drawbacks to having all portions after "col" in the
example above be *exactly* the same as the clauses allowed in CREATE
TABLE? So, this would be
ALTER TABLE tab ALTER COLUMN col [ NULL | NOT NULL ]
The syntax would then be entirely predictable if you knew what you would
have written if you had set the constraint during table creation. I'll
agree (if someone points it out) that this particular example is pretty
terse.
In that same line of thought, how about making it more closely mimic the
original CREATE TABLE syntax? Something like
ALTER TABLE t (c1 NULL)
Hmm. Or if we are going to eventually allow altering column types then
one could include the type also. That may be a bit much, but having an
idea of what *that* syntax might be could help on manipulating other
column attributes too...
- Thomas
(our mail crossed in the ether...)
> Let's say, theoretically, that in the future we want to allow people to
> change the type of their columns, plus allow them to change the nullability.
Right.
> Should we come up with a syntax for changing nullability that allows for the
> future changing of column type? If so, then a syntaxes like these might be
> the way to go:
Yup.
> If we just allow the full col spec we could one day support this:
> ALTER TABLE blah ALTER COLUMN col text boolean NOT NULL DEFAULT 'f';
> Which would change the column to that definition (if coercion is possible)
> no matter what current definition is...
Right. No point in *precluding* that with a short-sighted choice of
syntax.
> Is this the eventual goal? Will this cause shift/reduce errors? will we
> need to put the word 'SET' in after 'col'?
Probably not, if we can already do this with CREATE TABLE.
And if we head this direction, then choosing a syntax which most closely
mimics the current CREATE TABLE will allow altering two columns at once,
which would be more efficient presumably than doing one column at a
time.
- Thomas
At 18:34 20/02/02 -0800, Thomas Lockhart wrote: >> > ALTER TABLE blah ALTER COLUMN col [ALLOW NULL | NOT NULL] >> FWIW, I like this syntax too. > >What would be the drawbacks to having all portions after "col" in the >example above be *exactly* the same as the clauses allowed in CREATE >TABLE? So, this would be > > ALTER TABLE tab ALTER COLUMN col [ NULL | NOT NULL ] This looks fine to me. The spec only talks about CHECK constraints in ALTER TABLE, but if I had to guess the most spec-like syntax, it would be: ALTER TABLE tab ALTER COLUMN col DROP NOT NULL which does not seem particularly good; preserving the syntax from table creation has to be TWTG. Do we really allow: CREATE TABLE FOO(BAR INT NULL) ? >In that same line of thought, how about making it more closely mimic the >original CREATE TABLE syntax? Something like Because the SQL spec does have ALTER TABLE...ALTER COLUMN; so we should stick with the same syntax. >Hmm. Or if we are going to eventually allow altering column types then >one could include the type also. Definitely; Chris' suggestion seems pretty good to me. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
At 10:28 21/02/02 +0800, Christopher Kings-Lynne wrote: > >ALTER TABLE blah ALTER COLUMN col DROP DEFAULT; >ALTER TABLE blah ALTER COLUMN col SET DEFAULT 't'; >ALTER TABLE blah ALTER COLUMN col NULL; >ALTER TABLE blah ALTER COLUMN col NOT NULL; >ALTER TABLE blah ALTER COLUMN col varchar(50); >ALTER TABLE blah ALTER COLUMN col int4 NULL; >ALTER TABLE blah ALTER COLUMN col text NOT NULL; Looks good. >will we need to put the word 'SET' in after 'col'? The spec only uses SET for the DEFAULT clause. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
> ALTER TABLE tab ALTER COLUMN col DROP NOT NULL > > which does not seem particularly good; preserving the syntax from table > creation has to be TWTG. Do we really allow: > > CREATE TABLE FOO(BAR INT NULL) Certainly does. I depend on that ability to override the standard NULL / NOT NULL constraint that the domain may have to account for the exception to the rule. Actually, is that proper? Equally easy to disallow overrides, but (since the books I have don't say) it seemed useful for people with funny circumstances (like wanting to log a miss as well a hit).
The SQL spec will not help us here, since it doesn't define such a
capability AFAICT. We might do worse than to look at Or*cle's
implementation, which appears to involve a MODIFY keyword.
I find this in the Or*cle 8i documentation examples:
The following statement alters the EMP table and defines andenables a NOT NULL constraint on the SAL column:
ALTER TABLE emp MODIFY (sal NUMBER CONSTRAINT nn_sal NOT NULL);
The docs are opaque enough that I can't actually figure out a BNF
definition for ALTER TABLE MODIFY, and I don't have a working
installation to experiment against. Can any Or*cle users here
enlighten us?
regards, tom lane
> The SQL spec will not help us here, since it doesn't define such a > capability AFAICT. We might do worse than to look at Or*cle's > implementation, which appears to involve a MODIFY keyword. > > I find this in the Or*cle 8i documentation examples: > > The following statement alters the EMP table and defines and > enables a NOT NULL constraint on the SAL column: > > ALTER TABLE emp > MODIFY (sal NUMBER CONSTRAINT nn_sal NOT NULL); > > The docs are opaque enough that I can't actually figure out a BNF > definition for ALTER TABLE MODIFY, and I don't have a working > installation to experiment against. Can any Or*cle users here > enlighten us? I've already posted the Oracle and MSSQL spec to the list here - just check one of my earlier posts with this subject... A good place to ask questions is comp.databases.oracle.misc Chris
Christopher Kings-Lynne writes: > Should we come up with a syntax for changing nullability that allows for the > future changing of column type? If so, then a syntaxes like these might be > the way to go: > > ALTER TABLE blah ALTER COLUMN col DROP DEFAULT; > ALTER TABLE blah ALTER COLUMN col SET DEFAULT 't'; This is standard. > ALTER TABLE blah ALTER COLUMN col NULL; > ALTER TABLE blah ALTER COLUMN col NOT NULL; This is missing a verb. It can be read as "alter table blah, in particular, alter column col, (and do what with?) NULL". Is the NULL part of the identity of the column? Using the standard precedent above, how about ALTER TABLE blah ALTER COLUMN col SET NOT NULL; ALTER TABLE blah ALTER COLUMN col DROP NOT NULL; This also avoids the confusing "NULL constraint", which does not say that the column has to be NULL. > ALTER TABLE blah ALTER COLUMN col varchar(50); Here again, there should probably be at least one more word inserted, like TYPE. > If we just allow the full col spec we could one day support this: > > ALTER TABLE blah ALTER COLUMN col text boolean NOT NULL DEFAULT 'f'; Maybe ... ALTER COLUMN col TO text ... > Is this the eventual goal? Will this cause shift/reduce errors? will we > need to put the word 'SET' in after 'col'? A shift/reduce conflict has never stopped us. ;-) -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Using the standard precedent above, how about
> ALTER TABLE blah ALTER COLUMN col SET NOT NULL;
> ALTER TABLE blah ALTER COLUMN col DROP NOT NULL;
This seems like a good choice if we are not too concerned about
compatibility with other DBMSes. (Which, for something like this,
I'm not; how many applications will be issuing programmed commands
like this?)
regards, tom lane
Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Using the standard precedent above, how about > > > ALTER TABLE blah ALTER COLUMN col SET NOT NULL; > > ALTER TABLE blah ALTER COLUMN col DROP NOT NULL; > > This seems like a good choice if we are not too concerned about > compatibility with other DBMSes. (Which, for something like this, > I'm not; how many applications will be issuing programmed commands > like this?) Yes, I like this too; the SET/DROP symetry. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
My 2.2c worth (0.2c GST included):
The MySQL approach is a pain because it effectively makes you define a
field from scratch. You have to know and include all the field
attributes instead of just changing the attribute you want.
The attribute definition should be the same as used in create.
If the SQL standard does not have an appropriate facility, submit
yours as an enhancement. They can only say yes, no, or that they have
something already in the pipeline and then you can implement their
proposed standard.
Peter
On Thu, 21 Feb 2002 01:15:15 +0000 (UTC), chriskl@familyhealth.com.au
("Christopher Kings-Lynne") wrote:
>Hi guys,
>
>I've been chatting to Tom about implementing the ability to change the NULL
>status of a column via SQL.
>
>This is the Oracle syntax:
>
>alter table table_name modify column1 not null;
>alter table table_name modify column1 null;
>
>This is the MySQL syntax:
>
>ALTER TABLE asfd CHANGE [COLUMN] old_col_name create_definition [FIRST |
>AFTER column_name]
>or ALTER TABLE asfd MODIFY [COLUMN] create_definition [FIRST | AFTER
>column_name]
>
>CHANGE col_name, DROP col_name, and DROP INDEX are MySQL extensions to ANSI
>SQL92.
>MODIFY is an Oracle extension to ALTER TABLE.
>
>So, the question is - what the heck is the standard syntax? Is there a
>standard syntax? How about this syntax that I came up with:
>
>ALTER TABLE blah ALTER COLUMN col SET [NULL | NOT NULL]
>
>Anyone have any ideas? Perhaps we should use some sort of 'MODIFY'-like
>syntax to enable in the future maybe the ability to change column specs in
>more advanced ways (such as column type and size)
>
>If the answer is no, Postgres's parser does not have this syntax enabled,
>then I'm going to have to ask someone to implement it for me, and then I can
>fill in the actual guts of the function - whereever that may be. (I don't
>know parser stuff!)
>
>Chris
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
Hi, I'm halfway thru implementing setting a column's nullness (I've done changing to null, but not changing to not null) Peter E. said: > Using the standard precedent above, how about > > ALTER TABLE blah ALTER COLUMN col SET NOT NULL; > ALTER TABLE blah ALTER COLUMN col DROP NOT NULL; Do we want the above syntax, or this syntax: ALTER TABLE blah ALTER COLUMN col SET NOT NULL; ALTER TABLE blah ALTER COLUMN col SET NULL; The former sort of treats it like a contraint, where as the latter treats it as it is during the CREATE TABLE statement. Say in the future we want to support changing column type as well. How would we work that in? ALTER TABLE blah ALTER COLUMN col SET int4; ?????? Then we should allow people to do this: ALTER TABLE blah ALTER COLUMN col SET int4 NULL DEFAULT '3'; So they can change their entire column in one statement. So really this implies that ALTER COLUMN/SET NULL is the correct syntax, rather than ALTER COLUMN/DROP NOT NULL. In fact, maybe we could support BOTH syntaxes... Comments? Let's sort this out before I submit my patch. Regards, Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Say in the future we want to support changing column type as well. How
> would we work that in?
> ALTER TABLE blah ALTER COLUMN col SET int4; ??????
Seems one keyword shy of a load; I'd prefer
ALTER TABLE blah ALTER COLUMN col SET TYPE int4;
Otherwise, every keyword that might appear after SET will have to be
fully reserved (else it couldn't be distinguished from a type name).
I like the "SET NULL"/"SET NOT NULL" variant better than SET/DROP, even
though "SET NULL" is perhaps open to misinterpretation. "DROP NOT NULL"
seems just as confusing for anyone who's not read the documentation :-(
regards, tom lane
> Seems one keyword shy of a load; I'd prefer > > ALTER TABLE blah ALTER COLUMN col SET TYPE int4; > > Otherwise, every keyword that might appear after SET will have to be > fully reserved (else it couldn't be distinguished from a type name). I like that... So would you then envisage something like this: ALTER TABLE blah ALTER COLUMN col SET TYPE int4 DEFAULT 3 NOT NULL; or ALTER TABLE blah ALTER COLUMN col SET DEFAULT 3 TYPE int4 NULL; etc. ie. Order wouldn't matter and you could do them all at once for convenience? This seems like a cool idea to me. Problem with all this, of course, is that it's different to everyone else's syntax, but then they're all different to each other. There's no standard for it, but if there's a new standard - I wonder what they would specify? Since altering a column is a not oft used operation, I would expect that the punters wouldn't have a problem looking in the docs for how to do it, for each different DBMS they use... Chris
Christopher Kings-Lynne writes: > Do we want the above syntax, or this syntax: > > ALTER TABLE blah ALTER COLUMN col SET NOT NULL; > ALTER TABLE blah ALTER COLUMN col SET NULL; My only objection to the second command is that it's plain wrong. You don't set anything to NULL, so don't make the command look like it. -- Peter Eisentraut peter_e@gmx.net
Tom Lane wrote: > "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > > Say in the future we want to support changing column type as well. How > > would we work that in? > > > ALTER TABLE blah ALTER COLUMN col SET int4; ?????? > > Seems one keyword shy of a load; I'd prefer > > ALTER TABLE blah ALTER COLUMN col SET TYPE int4; > > Otherwise, every keyword that might appear after SET will have to be > fully reserved (else it couldn't be distinguished from a type name). > > I like the "SET NULL"/"SET NOT NULL" variant better than SET/DROP, even > though "SET NULL" is perhaps open to misinterpretation. "DROP NOT NULL" > seems just as confusing for anyone who's not read the documentation :-( Yes, DROP NOT NULL does have a weird twist to it. However, does SET NULL sound to much like you are setting all the values to NULL? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > Do we want the above syntax, or this syntax: > > > > ALTER TABLE blah ALTER COLUMN col SET NOT NULL; > > ALTER TABLE blah ALTER COLUMN col SET NULL; > > My only objection to the second command is that it's plain wrong. You > don't set anything to NULL, so don't make the command look like it. So then how is it any more wrong than SET NOT NULL? It should almost be ADD NOT NULL ... Chris
On Fri, Mar 22, 2002 at 02:34:57PM +0800, Christopher Kings-Lynne wrote: > > > Do we want the above syntax, or this syntax: > > > > > > ALTER TABLE blah ALTER COLUMN col SET NOT NULL; > > > ALTER TABLE blah ALTER COLUMN col SET NULL; > > > > My only objection to the second command is that it's plain wrong. You > > don't set anything to NULL, so don't make the command look like it. > > So then how is it any more wrong than SET NOT NULL? > > It should almost be ADD NOT NULL ... Hmm, there's this SQL92 keyword here: what do people thing of NULLABLE? SET NOT NULLABLE SET NULLABLE Ross
On March 22, 2002 01:31 am, Peter Eisentraut wrote:
> Christopher Kings-Lynne writes:
> > Do we want the above syntax, or this syntax:
> >
> > ALTER TABLE blah ALTER COLUMN col SET NOT NULL;
> > ALTER TABLE blah ALTER COLUMN col SET NULL;
>
> My only objection to the second command is that it's plain wrong. You
> don't set anything to NULL, so don't make the command look like it.
How about this?
ALTER TABLE blah ALTER COLUMN col UNSET NOT NULL;
I would almost think that it should be NOTNULL anyway to make it clear that we
are setting (or unsetting) one thing and that it is not a weird way of saying
"...NOT SET NULL" or "NOT UNSET NULL" but I realize that it should also look
more like the NOT NULL clause we already have in the CREATE TABLE query.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
> > Do we want the above syntax, or this syntax: > > > > ALTER TABLE blah ALTER COLUMN col SET NOT NULL; > > ALTER TABLE blah ALTER COLUMN col SET NULL; > > My only objection to the second command is that it's plain wrong. You > don't set anything to NULL, so don't make the command look like it. Imho it would be nice if the command would look exactly like a create table. It is simply convenient to use cut and paste :-) And I haven't seen a keyword yet, that would make it more descriptive, certainly not SET. ALTER TABLE blah ALTER [COLUMN] col [int4] [NOT NULL] [DEFAULT 32]; ALTER TABLE blah ALTER [COLUMN] col [int8] [NULL] [DEFAULT 32]; maybe even [DEFAULT NULL] to drop the default :-) Andreas
Christopher Kings-Lynne writes: > So then how is it any more wrong than SET NOT NULL? You're right. > It should almost be ADD NOT NULL ... I like that. It also makes sense because the standard syntax is to ADD/DROP CHECK constraints, to which NOT NULL constraints are equivalent. -- Peter Eisentraut peter_e@gmx.net
Zeugswetter Andreas SB SD wrote: > > Imho it would be nice if the command would look exactly like a create > table. It is simply convenient to use cut and paste :-) And I haven't > seen a keyword yet, that would make it more descriptive, certainly not SET. > > ALTER TABLE blah ALTER [COLUMN] col [int4] [NOT NULL] [DEFAULT 32]; > ALTER TABLE blah ALTER [COLUMN] col [int8] [NULL] [DEFAULT 32]; > maybe even [DEFAULT NULL] to drop the default :-) > I like this one. I would not make COLUMN optional though. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> ALTER TABLE blah ALTER [COLUMN] col [int4] [NOT NULL] [DEFAULT 32];
> ALTER TABLE blah ALTER [COLUMN] col [int8] [NULL] [DEFAULT 32];
This cannot work unless you are prepared to turn a lot more keywords
into reserved words. In the CREATE syntax, the data type is not
optional. In the above, there will be parse conflicts because the
system won't be able to decide whether a type name is present or not.
You could possibly make it work if you were willing to include the word
TYPE when trying to respecify column type:
ALTER TABLE blah ALTER [COLUMN] col [TYPE int4] [NOT NULL] [DEFAULT 32];
Also I agree with Fernando that trying to make the word COLUMN optional
is likely to lead to conflicts.
regards, tom lane
On Fri, Mar 22, 2002 at 01:12:09PM -0500, Tom Lane wrote: > > Also I agree with Fernando that trying to make the word COLUMN optional > is likely to lead to conflicts. According to the docs, COLUMN is _already_ optional at that point. Are the changes past that point going to cause different problems? Boy, parsers make my brain hurt. BTW, is NULLABLE so ugly that no one wanted to comment on it? It _is_ an sql92 reserved keyword, and it's actual english grammar. Ross
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> BTW, is NULLABLE so ugly that no one wanted to comment on it?
I kinda liked it, actually, if we were going to use the SET syntax.
But people seem to be focused in on this "let's make it look like
CREATE" notion. I'm willing to wait and see how far that can be made
to work.
regards, tom lane
On Fri, 2002-03-22 at 14:00, Ross J. Reedstrom wrote: > BTW, is NULLABLE so ugly that no one wanted to comment on it? It _is_ > an sql92 reserved keyword, and it's actual english grammar. FWIW, I liked it the best of all the solutions that have been proposed so far. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Tom Lane wrote: > "Ross J. Reedstrom" <reedstrm@rice.edu> writes: > > BTW, is NULLABLE so ugly that no one wanted to comment on it? > > I kinda liked it, actually, if we were going to use the SET syntax. > But people seem to be focused in on this "let's make it look like > CREATE" notion. I'm willing to wait and see how far that can be made > to work. OK, how about: SET CONSTRAINT NOT NULL or DROP CONSTRAINT NOT NULL or simply: SET/DROP NOT NULL I think the problem with trying to get it look like CREATE TABLE is that the plain NULL parameter to CREATE TABLE is meaningless and probably should never be used. I remember at one point pg_dump output NULL in the schema output and it confused many people. NOT NULL is the constraint, and I think any solution to remove NOT NULL has to include the NOT NULL keyword. I think this is also why SET NULL looks so bad. "CREATE TABLE test (x int NULL)" doesn't look great either. :-) What is that NULL doing there? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
...
> "CREATE TABLE test (x int NULL)" doesn't look great either. :-) What
> is that NULL doing there?
Well, because NOT NULL *was* in the standard, and because one should be
able to explicitly negate *that*. The alternative was
CREATE TABLE test (x int NOT NOT NULL)
:O
- Thomas
Thomas Lockhart wrote: > ... > > "CREATE TABLE test (x int NULL)" doesn't look great either. :-) What > > is that NULL doing there? > > Well, because NOT NULL *was* in the standard, and because one should be > able to explicitly negate *that*. The alternative was > > CREATE TABLE test (x int NOT NOT NULL) > > :O Yea, what I meant is that NULL doesn't look too clear in CREATE TABLE either. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> You could possibly make it work if you were willing to include the word > TYPE when trying to respecify column type: > > ALTER TABLE blah ALTER [COLUMN] col [TYPE int4] [NOT NULL] [DEFAULT 32]; > > Also I agree with Fernando that trying to make the word COLUMN optional > is likely to lead to conflicts. But all the other ALTER TABLE/Alter Column commands have it optional... I have throught of at least two problems with changing nullability. The first is primary keys. I have to prevent people setting a column involved in a PK to null, right? The second is DOMAINs - what if they change a NOT NULL domain in a colun to NULL? Shoudl I just outright prevent people from altering domain-based columns nullability> Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I have throught of at least two problems with changing nullability. The
> first is primary keys. I have to prevent people setting a column involved
> in a PK to null, right?
Probably so.
> The second is DOMAINs - what if they change a NOT NULL domain in a colun
> to NULL? Shoudl I just outright prevent people from altering domain-based
> columns nullability>
I don't think you need worry about this. The prototype DOMAIN
implementation is broken anyway --- it should not be transposing
domain constraints into column constraints, but should keep 'em
separate. The column-level attnotnull setting should be independent
of whether the domain enforces not-nullness or not.
regards, tom lane
> OK, how about: > > SET CONSTRAINT NOT NULL > > or > > DROP CONSTRAINT NOT NULL > > or simply: > > SET/DROP NOT NULL > > I think the problem with trying to get it look like CREATE TABLE is that > the plain NULL parameter to CREATE TABLE is meaningless and probably > should never be used. I remember at one point pg_dump output NULL in > the schema output and it confused many people. NOT NULL is the > constraint, and I think any solution to remove NOT NULL has to include > the NOT NULL keyword. I think this is also why SET NULL looks so bad. > "CREATE TABLE test (x int NULL)" doesn't look great either. :-) What > is that NULL doing there? OK, I've decided to go with: ALTER TABLE blah ALTER [COLUMN] col SET NOT NULL; and ALTER TABLE blah ALTER [COLUMN] col DROP NOT NULL; This is synchronous with the SET/DROP default stuff and is extensible in the future to fit in with column type changing. Of course, it can always be changed in the parser without affecting my code. Chris
> ALTER TABLE blah ALTER [COLUMN] col SET NOT NULL;
>
> and
>
> ALTER TABLE blah ALTER [COLUMN] col DROP NOT NULL;
>
> This is synchronous with the SET/DROP default stuff and is
> extensible in the
> future to fit in with column type changing.
>
> Of course, it can always be changed in the parser without
> affecting my code.
Also, in the future, once (if) the 'SET TYPE' column type changing function
has been implemented, we can create a meta-command to do it all in one
statement (for reliability and consistency for users). It could look like
this:
ALTER TABLE blah ALTER [COLUMN] col [SET TYPE type] [{SET | DROP} NOT NULL]
[{SET | DROP} DEFAULT [default]]
And a command like this should be able to just re-use already written code.
However, some interdependency checks might be more efficient if their done
before any changes are actually made! ie. Changing type to boolean and then
setting default to 'blah' in one statement, etc.
Chris
> Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > I have throught of at least two problems with changing nullability. The > > first is primary keys. I have to prevent people setting a > column involved > > in a PK to null, right? > > Probably so. What about temporary tables - is there any reason they shouldn't be able to modify a temporary table? What about indices? Will twiddling the nullability break indices on a table in any way? And foreign keys - foreign keys only have to reference UNIQUE, right? The nullability isn't an issue? Lastly - in a multicolumn primary key, does EVERY column in the key need to be NOT NULL? Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> What about temporary tables - is there any reason they shouldn't be able to
> modify a temporary table?
I don't see one.
> What about indices? Will twiddling the nullability break indices on a table
> in any way?
No, not as long as you aren't changing existing data in the table.
> And foreign keys - foreign keys only have to reference UNIQUE, right? The
> nullability isn't an issue?
Not sure about that --- Stephan or Jan will know.
> Lastly - in a multicolumn primary key, does EVERY column in the key need to
> be NOT NULL?
Yes, I believe so.
regards, tom lane
On Tue, 26 Mar 2002, Christopher Kings-Lynne wrote:
> > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> > > I have throught of at least two problems with changing nullability. The
> > > first is primary keys. I have to prevent people setting a
> > column involved
> > > in a PK to null, right?
> >
> > Probably so.
>
> And foreign keys - foreign keys only have to reference UNIQUE, right? The
> nullability isn't an issue?
That should be fine.
> Lastly - in a multicolumn primary key, does EVERY column in the key need to
> be NOT NULL?
Well, it looks like the primary key will not be satisfied if any of the
values are NULL.
In my SQL 92 draft, 11.7 Syntax Rules 3a says: If the <unique specification> specifies PRIMARY KEY, then let SC be
the<search condition>:
UNIQUE ( SELECT UCL FROM TN ) AND ( UCL ) IS NOT NULL