SQL Column Definition: Default Value and Not Null Redundant

SQL Column definition: default value and not null redundant?

DEFAULT is the value that will be inserted in the absence of an explicit value in an insert / update statement. Lets assume, your DDL did not have the NOT NULL constraint:

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) DEFAULT 'MyDefault'

Then you could issue these statements

-- 1. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B) VALUES (NULL, NULL);

-- 2. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, DEFAULT);

-- 3. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B, col) DEFAULT VALUES;

-- 4. This will insert NULL into tbl.col
INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, NULL);

Alternatively, you can also use DEFAULT in UPDATE statements, according to the SQL-1992 standard:

-- 5. This will update 'MyDefault' into tbl.col
UPDATE tbl SET col = DEFAULT;

-- 6. This will update NULL into tbl.col
UPDATE tbl SET col = NULL;

Note, not all databases support all of these SQL standard syntaxes. Adding the NOT NULL constraint will cause an error with statements 4, 6, while 1-3, 5 are still valid statements. So to answer your question: No, they're not redundant.

Change a Nullable column to NOT NULL with Default Value

I think you will need to do this as three separate statements. I've been looking around and everything i've seen seems to suggest you can do it if you are adding a column, but not if you are altering one.

ALTER TABLE dbo.MyTable
ADD CONSTRAINT my_Con DEFAULT GETDATE() for created

UPDATE MyTable SET Created = GetDate() where Created IS NULL

ALTER TABLE dbo.MyTable 
ALTER COLUMN Created DATETIME NOT NULL 

Can I add a not null column without DEFAULT value

No, you can't.

Because if you could, SQL wouldn't know what to put as value in the already existing records. If you didn't have any records in the table it would work without issues.

The simplest way to do this is create the column with a default and then remove the default.

ALTER TABLE dbo.MyTable ADD
MyColumn text NOT NULL CONSTRAINT DF_MyTable_MyColumn DEFAULT 'defaultValue'
ALTER TABLE dbo.MyTable
DROP CONSTRAINT DF_MyTable_MyColumn

Another alternative would be to add the column without the constraint, fill the values for all cells and add the constraint.

Why default is not used when I set not null contratint?

Per the documentation:

DEFAULT default_expr

(...)

The default expression will be used in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is null.

The altered default expression cannot modify rows already existing in the table, you should do it before setting the not null constraint:

update "order"
set last_bill_date = '-Infinity'
where last_bill_date is null

Is the not null constraint redundant if used with foreign key constraint?

It’s not redundant in this situation because such a constraint prevents omission of a permission_id. Sometimes you want a foreign keyed column to be nullable because it is not required (just as not every citizen in a citizens table went to a university, so a university_id column can be null). In other cases, the column should not be null, just as every student lshould be associated with a university_id.

In this situation, the permission_id is required because it is a not null column

Disclosure: I work for EnterpriseDB (EDB)

Sql table with default value will not save when passed a NULL

because you have set a contraint on that column and you have specified that a default value on that column is the current date GetDate()

Add a column with default value as a constant in Postgres

The issue was this:

DO $$
declare PARAGRAPH character varying(4000);
begin
    PARAGRAPH := 'Very large text goes in here.';
    
    ALTER TABLE USERS
    ADD COLUMN NOTES_TEXT character varying(4000) DEFAULT PARAGRAPH NOT NULL;
END $$;
ERROR:  cannot use column reference in DEFAULT expression
CONTEXT:  SQL statement "ALTER TABLE USERS
    ADD COLUMN NOTES_TEXT character varying(4000) DEFAULT PARAGRAPH NOT NULL"
PL/pgSQL function inline_code_block line 6 at SQL statement

The solution from here Dynamic SQL:

DO $$                                     
declare PARAGRAPH character varying(4000);
begin
    PARAGRAPH := 'Very large text goes in here.';
    
    EXECUTE 'ALTER TABLE USERS ' ||
    'ADD COLUMN NOTES_TEXT character varying(4000) DEFAULT' ||quote_literal(PARAGRAPH) || 'NOT NULL';
END $$;

\d users
...
 notes_text      | character varying(4000) |           | not null | 'Very large text goes in here.'::character varying

---

Related Topics