Ошибка sql column not null

I have tried:

ALTER TABLE MY_TABLE 
ADD STAGE INT NOT NULL;

But it gives this error message:

ALTER TABLE only allows columns to be added that can contain nulls or
have a DEFAULT definition specified

Ryan Kohn

13k12 gold badges55 silver badges80 bronze badges

asked Aug 16, 2010 at 12:10

As an option you can initially create Null-able column, then update your table column with valid not null values and finally ALTER column to set NOT NULL constraint:

ALTER TABLE MY_TABLE ADD STAGE INT NULL
GO
UPDATE MY_TABLE SET <a valid not null values for your column>
GO
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL
GO

Another option is to specify correct default value for your column:

ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL DEFAULT '0'

UPD: Please note that answer above contains GO which is a must when you run this code on Microsoft SQL server. If you want to perform the same operation on Oracle or MySQL you need to use semicolon ; like that:

ALTER TABLE MY_TABLE ADD STAGE INT NULL;
UPDATE MY_TABLE SET <a valid not null values for your column>;
ALTER TABLE MY_TABLE ALTER COLUMN STAGE INT NOT NULL;

answered Aug 16, 2010 at 12:14

Pavel MorshenyukPavel Morshenyuk

10.8k4 gold badges32 silver badges38 bronze badges

If you aren’t allowing the column to be Null you need to provide a default to populate existing rows. e.g.

ALTER TABLE dbo.YourTbl ADD
    newcol int NOT NULL CONSTRAINT DF_YourTbl_newcol DEFAULT 0

On Enterprise Edition this is a metadata only change since 2012

answered Aug 16, 2010 at 12:15

Martin SmithMartin Smith

435k87 gold badges737 silver badges836 bronze badges

A faster solution

If you, like me, need to do this on a table with a large amount of data, the ADD-UPDATE-ALTER option is very slow (it can take hours for millions of rows).

If you also don’t want a default value on your table, here’s the full code for creating a column and dropping the default constraint (pretty much instant even for large tables):

ALTER TABLE my_table ADD column_name INT NOT NULL CONSTRAINT my_table_default_constraint DEFAULT 0
GO

ALTER TABLE my_table DROP CONSTRAINT my_table_default_constraint 
GO

This is for SQL Server

answered Sep 23, 2021 at 14:06

JohanJohan

1,22516 silver badges34 bronze badges

The error message is quite descriptive, try:

ALTER TABLE MyTable ADD Stage INT NOT NULL DEFAULT '-';

answered Aug 16, 2010 at 12:14

a’ra’r

35.7k7 gold badges66 silver badges67 bronze badges

Other SQL implementations have similar restrictions. The reason is that adding a column requires adding values for that column (logically, even if not physically), which default to NULL. If you don’t allow NULL, and don’t have a default, what is the value going to be?

Since SQL Server supports ADD CONSTRAINT, I’d recommend Pavel’s approach of creating a nullable column, and then adding a NOT NULL constraint after you’ve filled it with non-NULL values.

answered Aug 16, 2010 at 12:42

dan04dan04

87.1k23 gold badges162 silver badges195 bronze badges

This worked for me, can also be «borrowed» from the design view, make changes -> right click -> generate change script.

BEGIN TRANSACTION
GO
ALTER TABLE dbo.YOURTABLE ADD
    YOURCOLUMN bit NOT NULL CONSTRAINT DF_YOURTABLE_YOURCOLUMN DEFAULT 0
GO
COMMIT

answered Oct 31, 2014 at 15:28

pabbiepabbie

1111 silver badge2 bronze badges

ALTER TABLE `MY_TABLE` ADD COLUMN `STAGE` INTEGER UNSIGNED NOT NULL AFTER `PREV_COLUMN`;

answered Aug 16, 2010 at 12:21

Alter TABLE 'TARGET' add 'ShouldAddColumn' Integer Not Null default "0"

Palle Due

5,8454 gold badges17 silver badges32 bronze badges

answered May 7, 2019 at 7:51

Источник

6 August 2019

Phil Factor explains the problems you might encounter when adding a non-nullable column to an existing table or altering a column that contains NULL values to be non-nullable. He demos a migration script that can deploy such changes safely. You might also learn that in an archaic form of the Scots language, used in Cumberland, the number 17 is «tiny bumfit»; I think the tiny bumfit bus goes from Penrith to Carlisle.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

It is a common ritual when designing a database to add or remove NULL constraints, but there are a couple of problems that can cause you grief when you are making changes to already-populated tables. This can happen when you try to add a new column that can’t accept NULL values, or to change an existing, nullable column into a NOT NULL column. SQL Prompt will warn you (EI028) if it detects code that will attempt to add a NOT NULL column to an existing table, without specifying a default value.

I’ll demonstrate these problems and then show you how to develop build scripts that apply these sorts of alterations. I’ll show how these can work regardless of whether you’re building a new version of the table from scratch, with the alterations, or if you need to update an existing table so that it incorporates these changes.

Adding a NOT NULL column to a populated table

We have a table, CountingWords, in which we record the words used to count, in Old Welsh. We have ambitions to count all the way up to 20, but currently only know how to count to 10.

/* we create a table. Just for our example, we create the words

used to count from one to ten in old Welsh.

(Na, nid wyf yn siaradwr Cymraeg ond rwy’n hoffi gwneud

rhywfaint o ymchwil ieithyddol gyda’r nos)

I’d like to do from eleven to twenty as well eventually

so I’ll add them and leave them NULL.

Here is the initial build script, with guard clauses

of course. We’ll re-create every time it is run.

With a few modifications we can make it so it only runs once

which is safer if you have reckless colleagues in your shop.

IF Object_Id(‘dbo.CountingWords’) IS NOT NULL DROP TABLE dbo.CountingWords;

—we script version 1 of our table of counting words

CREATE TABLE dbo.CountingWords

(

TheValue INT NOT NULL,

Word NVARCHAR(30) NULL,

CONSTRAINT CountingWordsPK PRIMARY KEY (TheValue)

);

INSERT INTO dbo.CountingWords (TheValue, Word)

VALUES

(1, ‘Un’), (2, ‘Dau’), (3, ‘Tri’), (4, ‘Pedwar’),

(5, ‘Pump’), (6, ‘Chwech’), (7, ‘Saith’), (8, ‘Wyth’),

(9, ‘Naw’), (10, ‘Deg’), (11, NULL), (12, NULL),

(13, NULL), (14, NULL), (15, NULL), (16, NULL),

(17, NULL), (18, NULL), (19, NULL), (20, NULL);

Listing 1: Un, Dau, Tri – version 1 of the CountingWords table

Having released the first version of this table, we realize quickly that we really should have recorded the name of the language, so we alter the design of the table to add a TheLanguage column, which cannot accept NULLs.

ALTER TABLE dbo.CountingWords ADD TheLanguage NVARCHAR(100) NOT NULL;

Immediately SQL Prompt warns us of the danger:

If we ignore SQL Prompt’s warnings and execute this, we will get an error.

Msg 4901, Level 16, State 1, Line 34
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'TheLanguage' cannot be added to non-empty table 'CountingWords' because it does not satisfy these conditions.

The error message is explicit, and it is easily fixed by defining a DEFAULT constraint so that SQL Server can insert a default value for this new column, for each row.

ALTER TABLE dbo.CountingWords ADD TheLanguage NVARCHAR(100) NOT NULL

DEFAULT ‘Old Welsh’;

Listing 2: Specifying a default when adding a NOT NULL column

Put simply, if a column is being added, and NULL values aren’t allowed, then you must provide a value to put into every row. As our table only had one language right now, ‘old welsh’, that wasn’t too hard.

Of course, we’ll want to record how to count in other languages too, such as Manx, Cornish or Cumbrian, so to enforce some data integrity, we need instead to create a parent table called Location that defines each of the languages, and where they were first recorded.

Let’s get that out the way. For this table, we can’t just drop and recreate the table without data-loss, and we’ll get an error anyway once our CountingWords table references this table via a FOREIGN KEY constraint. We need to take precautionary steps. I’ll use a simple, but rather strange, technique that ensures there is no damage, in terms of data loss, if the code is re-run. This script is obliged to run as several batches because CREATE TABLE statements must be at the start of a batch, and it is hard to execute code conditionally across batches.

IF Object_Id(‘dbo.Location’) IS NOT NULL SET NOEXEC ON;

—cunning way of only executing a section

—of code on a condition. until the next SET NOEXEC OFF

—we script version 1 of our table of counting words

—sadly the create table statement has to be at the start of a batch

CREATE TABLE dbo.Location

(

TheLanguage NVARCHAR(30) NOT NULL,

Description VARCHAR(100) NOT NULL DEFAULT »,

CONSTRAINT LanguageKey PRIMARY KEY (TheLanguage)

);

—now we insert the row we need for our existing data

INSERT INTO dbo.Location (TheLanguage) VALUES (‘Old Welsh’);

SET NOEXEC OFF;

Listing 3: Version 1 of the Location table

Of course, now we also need to modify the CountingWords table so that its TheLanguage column is a FOREIGN KEY, referencing the new Location table, but we’ll deal with that a little later.

Altering a nullable column to make it non-nullable

Quickly, we decide that allowing NULL values in TheWord column of CountingWords was a design mistake that we want to fix. We already learned that, if the table contains data, SQL Server won’t let us make a column non-nullable unless we provide a default value for it, in this case just a blank string.

ALTER TABLE CountingWords ADD CONSTRAINT WordConstraint DEFAULT » FOR Word;

ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

Msg 515, Level 16, State 2, Line 58
Cannot insert the value NULL into column 'Word', table 'PhilFactor.dbo.CountingWords'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Listing 4: Failed attempt to make the Word column NOT NULL

Aiee! We still can’t make column non-nullable, even though we’ve told SQL Server what to insert for NULL columns! First, we must remove any existing NULLs explicitly, by updating all the rows with the default value:

UPDATE CountingWords SET Word = DEFAULT WHERE Word IS NULL;

ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

Listing 5: Updating existing rows with the default value before making a column NOT NULL

So, that worked fine.

Rolling out all the changes

It’s time to roll out all these changes to people who only have version 1 of the design, where there was no Location table, and where CountingWords had no TheLanguage column and a nullable Word column.

Rolling out the new Location table, since it’s the first version of that table, is no real problem (see Listing 3). However, rolling out the changes to the new CountingWords table requires adding a TheLangauge column, which does not allow NULLs, and changing the Word column to be NOT NULL, in both cases avoiding the problems we already discussed.

We also want our migration script to work regardless of whether we’re updating an existing v1 of CountingWords, or we need to build v2 of the table from scratch. Also, we don’t want the script to cause any harm or trigger an error if it’s accidentally re-run.

As a bonus, in either case, we’ll also need to make the TheLangauge column in CountingWords a FOREIGN KEY that auto-updates in response to updates or deletes on the parent key, as well as alter the PRIMARY KEY. As a final step, we’ll add in the Old Welsh words for 11-20, which we previously didn’t know.

Here is the migration script that will either migrate CountingWords from v1 to v2 or else create v2 from scratch, and which will cause no harm if accidentally re-run. Before you try it, either drop the CountingWords table, or rerun Listing 1 to reestablish v1 of the table.

—we now script version 2

IF Object_Id(‘dbo.CountingWords’) IS NULL

BEGIN

—we script version 2 of our table of counting words if it

—doesn’t already exist

CREATE TABLE dbo.CountingWords

(

TheValue INT NOT NULL,

Word NVARCHAR(30) NOT NULL CONSTRAINT WordConstraint DEFAULT »,

TheLanguage NVARCHAR(30) NOT NULL

CONSTRAINT LanguageConstraint REFERENCES dbo.Location(TheLanguage)

ON DELETE CASCADE

ON UPDATE CASCADE

CONSTRAINT CountingWordsPK PRIMARY KEY(TheValue, TheLanguage)

);

END;

ELSE /* else we need to add a column and change the primary key

constraint */

BEGIN

IF NOT EXISTS — only run if the column does not exist

(

SELECT * FROM sys.columns

WHERE name LIKE ‘TheLanguage’

AND object_id = Object_Id(‘dbo.CountingWords’)

)

BEGIN

— first we need to add the language column

ALTER TABLE CountingWords ADD TheLanguage NVARCHAR(30) NOT NULL

DEFAULT ‘Old Welsh’ CONSTRAINT LanguageConstraint

REFERENCES dbo.Location(TheLanguage)

ON DELETE CASCADE

ON UPDATE CASCADE ;

END

—now we need to alter the primary key

ALTER TABLE CountingWords DROP CONSTRAINT CountingWordsPK;

ALTER TABLE CountingWords —and add the new version

ADD CONSTRAINT CountingWordsPK

PRIMARY KEY(TheValue, TheLanguage);

IF NOT EXISTS — do we need to add the default and remove the nulls?

(SELECT * FROM sys.default_constraints

WHERE name LIKE ‘WordConstraint’)

BEGIN

ALTER TABLE CountingWords ADD CONSTRAINT WordConstraint DEFAULT » FOR Word;

/* You can specify NOT NULL in ALTER COLUMN only if the column contains no null values.

The null values must be updated to some value before the ALTER COLUMN NOT NULL is allowed,*/

UPDATE CountingWords SET Word = DEFAULT WHERE Word IS NULL;

END;

IF NOT EXISTS —now finally we can make it not null

(SELECT * FROM sys.columns

WHERE name LIKE ‘word’ AND is_nullable = 0)

ALTER TABLE CountingWords ALTER COLUMN Word NVARCHAR(30) NOT NULL;

END;

IF EXISTS —do we need to add in the welsh words we didn’t know

(SELECT * FROM dbo.CountingWords

WHERE TheLanguage LIKE ‘Old Welsh’ AND word LIKE »

)

—yes we need to add those words to replace those pesky blanks

UPDATE CountingWords

SET Word = welsh.word

FROM CountingWords AS cw

INNER JOIN

(

VALUES (‘Un ar ddeg’, 11), (‘Deuddeg’, 12), (‘Tri ar ddeg’, 13),

(‘Pedwar ar ddeg’, 14), (‘Pymtheg’, 15), (‘Un ar bymtheg’, 16),

(‘Dau ar bymtheg’, 17), (‘Deunaw’, 18), (‘Pedwar ar bymtheg’, 19),

(‘Ugain’, 20)

) AS welsh (word, meaning)

ON welsh.meaning = cw.TheValue;

Listing 6: A safe migration script for v2 of CountingWords

If it wasn’t for trying to ensure that the migration script worked in all circumstances, it would have been a lot simpler. I just hate build scripts that can only be run in particular circumstances.

Let’s now test it out by adding the counting words from one to twenty in a different language/region:

DECLARE @Language NVARCHAR(30) = ‘West Cumbrian’;

IF NOT EXISTS

(SELECT * FROM dbo.Location

WHERE TheLanguage LIKE @Language

) INSERT INTO dbo.Location (TheLanguage)

VALUES (@Language);

/* and now we can add in any other ways of counting up to

twenty that we want */

IF NOT EXISTS (SELECT * FROM CountingWords WHERE Thelanguage LIKE @Language)

INSERT INTO CountingWords (TheValue, Word, TheLanguage)

SELECT TheValue, word, @Language

FROM

(

VALUES (‘yan’, 1), (‘tyan’, 2), (‘tethera’, 3), (‘methera’, 4),

(‘pimp’, 5), (‘sethera’, 6), (‘lethera’, 7), (‘hovera’, 8),

(‘dovera’, 9), (‘dick’, 10), (‘yan-a-dick’, 11), (‘tyan-a-dick’, 12),

(‘tethera-dick’, 13), (‘nethera-dick’, 14), (‘bumfit’, 15),

(‘yan-a-bumfit’, 16), (‘tyan-a-bumfi t’, 17), (‘tithera-bumfit’, 18),

(‘methera-bumfit’, 19), (‘giggot’, 20)

) AS f (word, TheValue);

Listing 7: Yan, Tyan, Tethera – counting to 20 in West Cumbria

Now I’ve changed my mind. It shouldn’t be called ‘Old Welsh’, but ‘Archaic Welsh’. What a shame, but then we can now test out our foreign key constraint.

UPDATE dbo.Location SET TheLanguage = ‘Archaic Welsh’ WHERE TheLanguage LIKE ‘Old Welsh’

SELECT * FROM location

SELECT * FROM CountingWords

Listing 8: Cascading updates after changing the language

As if by magic, all the references have changed. I now have a database I can use!

I’ve provided a FillCountingWordsTable script, containing a full set of counting words for a variety of 47 recorded locations and languages, that you can use for more extensive testing. Apologies to Stateside friends and relatives that I left out the several Indian tribes who used the same counting rhymes. It turned out on investigation that they’d been taught knitting by the British colonists, and they’d thought that the words used to count the stitches were part of the magic.

Conclusion

We’ve just set up a rather elaborate demonstration of how to avoid some of the problems of altering tables that are already populated with data. In this example, the problems involve the use of NULL values, and happen when you try to add a new column that can’t accept NULL values, or to change an existing, nullable column into a NOT NULL column, when there are existing NULL values in the column. In the first case, you simply add a DEFAULT constraint to the column first, with a value that isn’t NULL, and in the second case you remove the NULL values first by updating the table.

These are techniques for making changes to existing tables, which is why I elaborated the demonstration to illustrate how to go about doing both of these operations as part of a resilient script that can be run regardless of whether it is a fresh build or a migration, and that can be re-run without any detrimental effects.

Tools in this post

Nullable Columns

The SQL Server by default allows storing the NULL value in a column. We call such columns Nullable columns

The Following example creates Employee Table. SQL Server creates all the above columns as nullable columns.

CREATE TABLE Employee (

EmployeeID [int] ,

FirstName [varchar](50) ,

LastName [varchar](50) ,

Department [varchar](20) ,

)

This means that you can insert null into them.

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (null, null,null,null)

Select * from Employee

EmployeeID FirstName LastName Department

———- ——— ——— ———-

NULL NULL NULL NULL

To make a column Not Nullable use the NOT NULL Constraint on the column.

The following query creates the Employee Table with EmployeeID & FirstName & LastName as Non-Nullable Columns.

CREATE TABLE Employee (

EmployeeID [int] NOT NULL ,

FirstName [varchar](50) NOT NULL ,

LastName [varchar](50) NOT NULL ,

Department [varchar](20) NULL ,

)

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (1,‘Olive’,‘Yew’)

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (2,‘Aida’,‘Bugg’,null)

Select * from Employee

EmployeeID FirstName LastName Department

———- ——— ——— ———-

1 Olive Yew NULL

2 Aida Bugg NULL

When trying to insert a NULL into a not nullable column like FirstName, SQL Server throws “column does not allow nulls” error.

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (3,null,‘Austin’,‘Accounts’)

Cannot insert the value NULL into column ‘FirstName’, table ‘DB.dbo.Employee’;

column does not allow nulls. INSERT fails.

The statement has been terminated.

Naming the NOT NULL Constraint

SQL Server stores the NOT NULL as a boolean attribute of the column. It does not store it anywhere else. Hence you cannot give a name to the NOT NULL Constraint.

But, Syntax allows you to name the constraint. The following query creates a NOT NULL constraint with the name NN_Employee_EmployeeID. But SQL Server discards it.

CREATE TABLE Employee (

EmployeeID [int] CONSTRAINT NN_Employee_EmployeeID NOT NULL,

)

Hence deleting the NOT NULL constraint using the name will result in an error.

Alter table Employee DROP CONSTRAINT NN_Employee_EmployeeID

*** Result ***

‘NN_Employee_EmployeeID’ is not a constraint.

Could not drop constraint. See previous errors.

Modify Exitsting Column to NOT NULL

You can use Alter Table query to change the nullability of an existing column.

But the SQL Server will not convert a NULL column to NOT NULL if the column currently contains null values.

The Employee table below contains the Department column which is a Nullable column.

CREATE TABLE Employee (

EmployeeID [int] NOT NULL ,

FirstName [varchar](50) NOT NULL ,

LastName [varchar](50) NOT NULL ,

Department [varchar](20) NULL ,

)

The following queries insert the null value to the department column.

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (1,‘Olive’,‘Yew’,‘Admin’)

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (2,‘Aida’,‘Bugg’,null) //Department is NULL

When we try to change the Department to NOT NULL, the SQL server throws the “Cannot insert the value NULL into column” error.

Alter Table Employee Alter Column Department [varchar](20) NOT NULL

*** ERROR ***

Cannot insert the value NULL into column ‘Department’, table ‘DB.dbo.Employee’; column does not allow nulls. UPDATE fails.

To solve this issue, first, we need to update the Department column and remove all NULL values. The following query updates the Department column to an empty space where its value is NULL.

Update Employee Set Department=» Where Department is NULL

Now, you can change the Department column to NOT NULL

Alter Table Employee Alter Column Department [varchar](20) NOT NULL

Add NOT NULL Column to existing table

To change a column from NOT NULL to NULL run the following query.

//Default is NULL

Alter Table Employee Alter Column Department [varchar](20)

Alter Table Employee Alter Column Department [varchar](20) NULL

Read More

SQL Server Tutorial
Constraints in SQL Server
Default Constraint
NOT NULL Constraint
Primary Key in SQL Server
Foreign Key in SQL Server
Unique Key in SQL Server
Check Constraint

Источник

Summary: in this tutorial, you will learn how to use a not null constraint for a column to ensure values stored in the column are not null.

Introduction to the MariaDB `not null` constraint

The not null is a domain integrity constraint that ensures values stored in a column are not null.

Here is the syntax of defining a not null constraint:

column_name datatype not null;
Code language: SQL (Structured Query Language) (sql)

If you insert or update null to a column that has a not null constraint, MariaDB rejects the change and issues an error.

The following statement creates a table called courses:

create table courses(
    course_id int auto_increment,
    course_name varchar(100) not null,
    summary varchar(255),
    primary key(course_id)
);
Code language: SQL (Structured Query Language) (sql)

The course_id is the primary key of the courses table, therefore, it doesn’t accept null values because it has an implicit not null constraint.

The course_name has the not null constraint so it also doesn’t accept null values.

This statement attempts to insert a null value into the course_name column:

insert into courses(course_name)
values(null);
Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following error:

SQL Error (1048): Column 'course_name' cannot be null
Code language: SQL (Structured Query Language) (sql)

Adding a `not null` constraint to an existing column

To add a not null constraint to an existing column, you follow these steps:

First, update null values to a non-null value if available.
Second, modify the column to include a not null constraint.

For example, to add a not null constraint to the summary column of the courses table, you use these steps:

First, update null values in the summary column to non-null values:

update courses
set summary = 'N/A'
where summary is null;
Code language: SQL (Structured Query Language) (sql)

Second, modify the summary column to include a not null constraint:

alter table courses
modify summary varchar(255) not null;
Code language: SQL (Structured Query Language) (sql)

Removing a `not null` constraint

To remove a not null constraint from a column, you use the alter table modify statement:

alter table table_name
modify column_name datatype;
Code language: SQL (Structured Query Language) (sql)

For example, the following statement removes the not null constraint from the summary column of the courses table:

alter table courses
modify summary varchar(255);
Code language: SQL (Structured Query Language) (sql)

To verify whether the not null constraint has been removed, you can use the describe statement:

describe courses;
Code language: SQL (Structured Query Language) (sql)

Article

Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

SQL Prompt can help prevent the build-up of technical debt in your database code. It alerts the team immediately to issues that might not prevent your code from working as expected, but could eventually cause problems with performance, or maintenance, or even security.

Article

Quick extended properties with SQL Prompt

I’ve been experimenting with Extended Properties, and I found myself slightly annoyed by the syntax of adding and updating Extended Properties. I decided to take advantage of SQL Prompt to store the commonly used code for adding and updating properties. Adding Properties I’m a big fan of naming the snippets the first thing that comes to mind.

Источник

Nullable Columns

The SQL Server by default allows storing the NULL value in a column. We call such columns Nullable columns

The Following example creates Employee Table. SQL Server creates all the above columns as nullable columns.

CREATE TABLE Employee (

EmployeeID [int] ,

FirstName [varchar](50) ,

LastName [varchar](50) ,

Department [varchar](20) ,

)

This means that you can insert null into them.

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (null, null,null,null)

Select * from Employee

EmployeeID FirstName LastName Department

———- ——— ——— ———-

NULL NULL NULL NULL

To make a column Not Nullable use the NOT NULL Constraint on the column.

The following query creates the Employee Table with EmployeeID & FirstName & LastName as Non-Nullable Columns.

CREATE TABLE Employee (

EmployeeID [int] NOT NULL ,

FirstName [varchar](50) NOT NULL ,

LastName [varchar](50) NOT NULL ,

Department [varchar](20) NULL ,

)

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (1,‘Olive’,‘Yew’)

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (2,‘Aida’,‘Bugg’,null)

Select * from Employee

EmployeeID FirstName LastName Department

———- ——— ——— ———-

1 Olive Yew NULL

2 Aida Bugg NULL

When trying to insert a NULL into a not nullable column like FirstName, SQL Server throws “column does not allow nulls” error.

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (3,null,‘Austin’,‘Accounts’)

Cannot insert the value NULL into column ‘FirstName’, table ‘DB.dbo.Employee’;

column does not allow nulls. INSERT fails.

The statement has been terminated.

Naming the NOT NULL Constraint

SQL Server stores the NOT NULL as a boolean attribute of the column. It does not store it anywhere else. Hence you cannot give a name to the NOT NULL Constraint.

But, Syntax allows you to name the constraint. The following query creates a NOT NULL constraint with the name NN_Employee_EmployeeID. But SQL Server discards it.

CREATE TABLE Employee (

EmployeeID [int] CONSTRAINT NN_Employee_EmployeeID NOT NULL,

)

Hence deleting the NOT NULL constraint using the name will result in an error.

Alter table Employee DROP CONSTRAINT NN_Employee_EmployeeID

*** Result ***

‘NN_Employee_EmployeeID’ is not a constraint.

Could not drop constraint. See previous errors.

Modify Exitsting Column to NOT NULL

You can use Alter Table query to change the nullability of an existing column.

But the SQL Server will not convert a NULL column to NOT NULL if the column currently contains null values.

The Employee table below contains the Department column which is a Nullable column.

CREATE TABLE Employee (

EmployeeID [int] NOT NULL ,

FirstName [varchar](50) NOT NULL ,

LastName [varchar](50) NOT NULL ,

Department [varchar](20) NULL ,

)

The following queries insert the null value to the department column.

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (1,‘Olive’,‘Yew’,‘Admin’)

insert into Employee (EmployeeID, FirstName, LastName, Department)

values (2,‘Aida’,‘Bugg’,null) //Department is NULL

When we try to change the Department to NOT NULL, the SQL server throws the “Cannot insert the value NULL into column” error.

Alter Table Employee Alter Column Department [varchar](20) NOT NULL

*** ERROR ***

Cannot insert the value NULL into column ‘Department’, table ‘DB.dbo.Employee’; column does not allow nulls. UPDATE fails.

To solve this issue, first, we need to update the Department column and remove all NULL values. The following query updates the Department column to an empty space where its value is NULL.

Update Employee Set Department=» Where Department is NULL

Now, you can change the Department column to NOT NULL

Alter Table Employee Alter Column Department [varchar](20) NOT NULL

Add NOT NULL Column to existing table

To change a column from NOT NULL to NULL run the following query.

//Default is NULL

Alter Table Employee Alter Column Department [varchar](20)

Alter Table Employee Alter Column Department [varchar](20) NULL

Read More

SQL Server Tutorial
Constraints in SQL Server
Default Constraint
NOT NULL Constraint
Primary Key in SQL Server
Foreign Key in SQL Server
Unique Key in SQL Server
Check Constraint

Источник

Summary: in this tutorial, you will learn how to use a not null constraint for a column to ensure values stored in the column are not null.

Introduction to the MariaDB `not null` constraint

The not null is a domain integrity constraint that ensures values stored in a column are not null.

Here is the syntax of defining a not null constraint:

column_name datatype not null;
Code language: SQL (Structured Query Language) (sql)

If you insert or update null to a column that has a not null constraint, MariaDB rejects the change and issues an error.

The following statement creates a table called courses:

create table courses(
    course_id int auto_increment,
    course_name varchar(100) not null,
    summary varchar(255),
    primary key(course_id)
);
Code language: SQL (Structured Query Language) (sql)

The course_id is the primary key of the courses table, therefore, it doesn’t accept null values because it has an implicit not null constraint.

The course_name has the not null constraint so it also doesn’t accept null values.

This statement attempts to insert a null value into the course_name column:

insert into courses(course_name)
values(null);
Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following error:

SQL Error (1048): Column 'course_name' cannot be null
Code language: SQL (Structured Query Language) (sql)

Adding a `not null` constraint to an existing column

To add a not null constraint to an existing column, you follow these steps:

First, update null values to a non-null value if available.
Second, modify the column to include a not null constraint.

For example, to add a not null constraint to the summary column of the courses table, you use these steps:

First, update null values in the summary column to non-null values:

update courses
set summary = 'N/A'
where summary is null;
Code language: SQL (Structured Query Language) (sql)

Second, modify the summary column to include a not null constraint:

alter table courses
modify summary varchar(255) not null;
Code language: SQL (Structured Query Language) (sql)

Removing a `not null` constraint

To remove a not null constraint from a column, you use the alter table modify statement:

alter table table_name
modify column_name datatype;
Code language: SQL (Structured Query Language) (sql)

For example, the following statement removes the not null constraint from the summary column of the courses table:

alter table courses
modify summary varchar(255);
Code language: SQL (Structured Query Language) (sql)

To verify whether the not null constraint has been removed, you can use the describe statement:

describe courses;
Code language: SQL (Structured Query Language) (sql)

or use show create table statement:

show create table courses;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the not null constraint for a column to ensure values stored in the column are not null.

Was this tutorial helpful ?

Источник

Summary: in this tutorial, you will learn about PostgreSQL not-null constraint to ensure the values of a column are not null.

Introduction to NULL

In database theory, NULL represents unknown or information missing. NULL is not the same as an empty string or the number zero.

Suppose that you need to insert an email address of a contact into a table. You can request his or her email address. However, if you don’t know whether the contact has an email address or not, you can insert NULL into the email address column. In this case, NULL indicates that the email address is not known at the time of recording.

NULL is very special. It does not equal anything, even itself. The expression NULL = NULL returns NULL because it makes sense that two unknown values should not be equal.

To check if a value is NULL or not, you use the IS NULL boolean operator. For example, the following expression returns true if the value in the email address is NULL.

email_address IS NULLCode language: PHP (php)

The IS NOT NULL operator negates the result of the IS NULL operator.

To control whether a column can accept NULL, you use the NOT NULL constraint:

CREATE TABLE table_name(
   ...
   column_name data_type NOT NULL,
   ...
);Code language: PHP (php)

If a column has a NOT NULL constraint, any attempt to insert or update NULL in the column will result in an error.

Declaring NOT NULL columns

The following CREATE TABLE statement creates a new table name invoices with the not-null constraints.

CREATE TABLE invoices(
  id SERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  qty numeric NOT NULL CHECK(qty > 0),
  net_price numeric CHECK(net_price > 0) 
);Code language: SQL (Structured Query Language) (sql)

This example uses the NOT NULL keywords that follow the data type of the product_id and qty columns to declare NOT NULL constraints.

Note that a column can have multiple constraints such as NOT NULL, check, unique, foreign key appeared next to each other. The order of the constraints is not important. PostgreSQL can check the constraint in the list in any order.

If you use NULL instead of NOT NULL, the column will accept both NULL and non-NULL values. If you don’t explicitly specify NULL or NOT NULL, it will accept NULL by default.

Adding NOT NULL Constraint to existing columns

To add the NOT NULL constraint to a column of an existing table, you use the following form of the ALTER TABLE statement:

ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;Code language: SQL (Structured Query Language) (sql)

To add set multiple NOT NULL constraint to multiple columns, you use the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name_1 SET NOT NULL,
ALTER COLUMN column_name_2 SET NOT NULL,
...;Code language: SQL (Structured Query Language) (sql)

Let’s take a look at the following example.

First, create a new table called production orders ( production_orders):

CREATE TABLE production_orders (
	id SERIAL PRIMARY KEY,
	description VARCHAR (40) NOT NULL,
	material_id VARCHAR (16),
	qty NUMERIC,
	start_date DATE,
	finish_date DATE
);Code language: SQL (Structured Query Language) (sql)

Next, insert a new row into the production_orders table:

INSERT INTO production_orders (description)
VALUES('Make for Infosys inc.');Code language: SQL (Structured Query Language) (sql)

Then, to make sure that the qty field is not null, you can add the not-null constraint to the qty column. However, the column already contains data. If you try to add the not-null constraint, PostgreSQL will issue an error.

To add the NOT NULL constraint to a column that already contains NULL, you need to update NULL to non-NULL first, like this:

UPDATE production_orders
SET qty = 1;Code language: SQL (Structured Query Language) (sql)

The values in the qty column are updated to one. Now, you can add the NOT NULL constraint to the qty column:

ALTER TABLE production_orders 
ALTER COLUMN qty
SET NOT NULL;Code language: SQL (Structured Query Language) (sql)

After that, you can update the not-null constraints for material_id, start_date, and finish_date columns:

UPDATE production_orders
SET material_id = 'ABC',
    start_date = '2015-09-01',
    finish_date = '2015-09-01';Code language: SQL (Structured Query Language) (sql)

Add not-null constraints to multiple columns:

ALTER TABLE production_orders 
ALTER COLUMN material_id SET NOT NULL,
ALTER COLUMN start_date SET NOT NULL,
ALTER COLUMN finish_date SET NOT NULL;Code language: SQL (Structured Query Language) (sql)

Finally, attempt to update values in the qty column to NULL:

UPDATE production_orders
SET qty = NULL;Code language: SQL (Structured Query Language) (sql)

PostgreSQL issued an error message:

[Err] ERROR:  null value in column "qty" violates not-null constraint
DETAIL:  Failing row contains (1, make for infosys inc., ABC, null, 2015-09-01, 2015-09-01).Code language: JavaScript (javascript)

The special case of NOT NULL constraint

Besides the NOT NULL constraint, you can use a CHECK constraint to force a column to accept not NULL values. The NOT NULL constraint is equivalent to the following CHECK constraint:

CHECK(column IS NOT NULL)Code language: SQL (Structured Query Language) (sql)

This is useful because sometimes you may want either column a or b is not null, but not both.

For example, you may want either username or email column of the user tables is not null or empty. In this case, you can use the CHECK constraint as follows:

CREATE TABLE users (
 id serial PRIMARY KEY,
 username VARCHAR (50),
 password VARCHAR (50),
 email VARCHAR (50),
 CONSTRAINT username_email_notnull CHECK (
   NOT (
     ( username IS NULL  OR  username = '' )
     AND
     ( email IS NULL  OR  email = '' )
   )
 )
);Code language: SQL (Structured Query Language) (sql)

The following statement works.

INSERT INTO users (username, email)
VALUES
	('user1', NULL),
	(NULL, 'email1@example.com'),
	('user2', 'email2@example.com'),
	('user3', '');Code language: SQL (Structured Query Language) (sql)

However, the following statement will not work because it violates the CHECK constraint:

INSERT INTO users (username, email)
VALUES
	(NULL, NULL),
	(NULL, ''),
	('', NULL),
	('', '');Code language: SQL (Structured Query Language) (sql)

[Err] ERROR:  new row for relation "users" violates check constraint "username_email_notnull"Code language: JavaScript (javascript)

Summary

Use the NOT NULL constraint for a column to enforce a column not accept NULL. By default, a column can hold NULL.
To check if a value is NULL or not, you use the IS NULL operator. The IS NOT NULL negates the result of the IS NULL.
Never use equal operator = to compare a value with NULL because it always returns NULL.

Was this tutorial helpful ?

Источник

Ошибка spin tires mudrunner could not load config

Ошибка sql 1046 no database selected

Ошибка spotify этот трек недоступен

Ошибка spn 791 fmi 5 камаз 6520 евро

Ошибка spn 790 fmi 5 камаз камминз

Guest post

Adding a NOT NULL column to a populated table

Altering a nullable column to make it non-nullable

Rolling out all the changes

Conclusion

Tools in this post

You may also like

Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

Quick extended properties with SQL Prompt

Nullable Columns

Naming the NOT NULL Constraint

Modify Exitsting Column to NOT NULL

Add NOT NULL Column to existing table

Introduction to the MariaDB not null constraint

Adding a not null constraint to an existing column

Removing a not null constraint

Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

Quick extended properties with SQL Prompt

Nullable Columns

Naming the NOT NULL Constraint

Modify Exitsting Column to NOT NULL

Add NOT NULL Column to existing table

Introduction to the MariaDB not null constraint

Adding a not null constraint to an existing column

Removing a not null constraint

Introduction to NULL

Declaring NOT NULL columns

Adding NOT NULL Constraint to existing columns

The special case of NOT NULL constraint

Summary

Introduction to the MariaDB `not null` constraint

Adding a `not null` constraint to an existing column

Removing a `not null` constraint

Introduction to the MariaDB `not null` constraint

Adding a `not null` constraint to an existing column

Removing a `not null` constraint