Unable to add NOT NULL constraint (Msg 102, Level 15, State 1)

Question

Unable to add NOT NULL constraint (Msg 102, Level 15, State 1)

joJo4146

SSC Enthusiast

Points: 113
More actions
September 4, 2022 at 7:15 pm
Go to Answer
#4083805
Beginner here and trying to solve a problem from which I cannot find any clear solutions. I am supposed to alter a table. I am supposed to add two constraints, these being the addition of a primary key and the addition of a foreign key (Some_ID). However, Some_ID is NULL and I am supposed to modify/alter the column into NOT NULL. I cannot find the solution to that. I am getting a Msg 102, Level 15, State 1 syntax error near the 'int'. Sorry if this has been discussed to no end but I do not get what is the issue here. Perhaps a recommendation or a link would be appreciated. Thanks.
```
ALTER TABLE KnownTable
ADD CONSTRAINT PK_KnownTable PRIMARY KEY (Known_iD),
CONSTRAINT FK_KnownTable FOREIGN KEY (Some_ID) REFERENCES Other_Table(Some_ID),
MODIFY Some_ID int NOT NULL;
```
Jeff Moden

SSC Guru

Points: 1004323
More actions
September 4, 2022 at 11:38 pm
Answer
#4083836

You simply can't constrain a column to NOT NULL if it contains NULLs. There has to be a value in every row of a NOT NULL column or one that you wish to make NOT NULL.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

SomeID doesn't have to have a value to have the FK placed on it. FK's are "if something, other than a NULL, is present, it has to be in that other table".

If the powers that be insist on SomeID not being NULL, you need to decide what value all those NULLs should be AND that something needs to be in the other table.

A better thing would be to tell the powers that be of this problem and ask them what value they want to be assigned for the NULLs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

joJo4146 SSC Enthusiast Points: 113 More actions · Answer 2

Thank you for your reply. This is all I have to work with for now but the MODIFY or ALTER COLUMN is not doing it for some reason.

drew.allen SSC Guru Points: 76985 More actions · Answer 3

To put it another way, you have to first update the existing table to replace all NULL values in that column with non-NULL values before you can alter the table to make that column NOT NULL.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Or, setup a Default and use the WITH VALUES option,

The end result is the same though. There cannot be any NULLs in a NOT NULL column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

Jeff Moden wrote:

Or, setup a Default and use the WITH VALUES option,
The end result is the same though. There cannot be any NULLs in a NOT NULL column.

Why did it take me 10 years to learn about this "WITH VALUES" option ???

2022-09-07 08_06_11-column_definition (Transact-SQL) - SQL Server _ Microsoft Docs

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

Johan Bijnens wrote:

Jeff Moden wrote:
Or, setup a Default and use the WITH VALUES option,
The end result is the same though. There cannot be any NULLs in a NOT NULL column.
Why did it take me 10 years to learn about this "WITH VALUES" option ???

😀 For the same reason that it took me a while to learn that TRIM is much more than just a combo of LTRIM/RTRIM. 😀 No one has the time to keep up with everything.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)