- Stack Overflow Public questions & answers
- Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
- Talent Build your employer brand
- Advertising Reach developers & technologists worldwide
- About the company
Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Explicitly set column value to null SQL Developer
I am new to Oracle DB and I am using Oracle SQL Developer (Ver 3.0.02) to query the DB. I wanted to explicitly set one column to null?
How do I do that in the SQL Developer GUI?
Previously in MSSQL, clicking CTRL+0 will explicitly set the value to null. How about Oracle SQL Developer? Thanks
- I'm not sure I understand the question. Are you trying to add a column to a result set that is always NULL? Update a column in a table so that it is always NULL? Allow NULL values in a particular column? Something else? – Justin Cave Aug 22, 2011 at 3:35
- @Justin I want to explicitly set the column to null. Say I click a column and then sets its value to null explicitly. I am actually looking for a shortcut key for this. – Mark Estrada Aug 22, 2011 at 3:43
- I'm still not sure I understand. Is your intention to update a column in a table so that the value in every row is NULL? To modify the constraints on a table to allow NULL values in a column? Something else? – Justin Cave Aug 22, 2011 at 4:44
4 Answers 4
You'll have to write the SQL DML yourself explicitly. i.e.
Once it has completed you'll need to commit your updates
If you only want to set certain records to NULL use a WHERE clause in your UPDATE statement.
As your original question is pretty vague I hope this covers what you want.
If you want to use the GUI... click/double-click the table and select the Data tab. Click in the column value you want to set to (null) . Select the value and delete it. Hit the commit button (green check-mark button). It should now be null.
More info here:
How to use the SQL Worksheet in SQL Developer to Insert, Update and Delete Data
- You need to hit Ctrl + Shift keys to delete the rows. – cнŝdk Jun 15, 2017 at 13:03
More info: Shift+Del combination key set a field to null when you filled a field by a value and you changed your decision and you want to make it null. It is useful and I amazed from the other answers that give strange solutions.
- Maybe this combination wasn't available in 2011, but today this should be the correct answer. – Alexander Nov 30, 2018 at 22:33
- @Alexander I think I tested my answer with sql developer 3.0. – Mostafa Vatanpour Nov 30, 2018 at 23:07
It is clear that most people who haven't used SQL Server Enterprise Manager don't understand the question (i.e. Justin Cave).
I came upon this post when I wanted to know the same thing.
Using SQL Server, when you are editing your data through the MS SQL Server GUI Tools, you can use a KEYBOARD SHORTCUT to insert a NULL rather than having just an EMPTY CELL, as they aren't the same thing. An empty cell can have a space in it, rather than being NULL, even if it is technically empty. The difference is when you intentionally WANT to put a NULL in a cell rather than a SPACE or to empty it and NOT using a SQL statement to do so.
So, the question really is, how do I put a NULL value in the cell INSTEAD of a space to empty the cell?
I think the answer is, that the way the Oracle Developer GUI works, is as Laniel indicated above, And THAT should be marked as the answer to this question.
Oracle Developer seems to default to NULL when you empty a cell the way the op is describing it.
Additionally, you can force Oracle Developer to change how your null cells look by changing the color of the background color to further demonstrate when a cell holds a null:
Tools->Preferences->Advanced->Display Null Using Background Color
or even the VALUE it shows when it's null:
Tools->Preferences->Advanced->Display Null Value As
Hope that helps in your transition.
Sign up or log in, post as a guest.
Required, but never shown
Not the answer you're looking for? Browse other questions tagged oracle null oracle11g oracle-sqldeveloper plsqldeveloper or ask your own question .
- The Overflow Blog
- How Intuit democratizes AI development across teams through reusability sponsored post
- The nature of simulating nature: A Q&A with IBM Quantum researcher Dr. Jamie...
- Featured on Meta
- We've added a "Necessary cookies only" option to the cookie consent popup
- Launching the CI/CD and R Collectives and community editing features for...
- Staging Ground Beta 1 Recap, and Reviewers needed for Beta 2
- The [amazon] tag is being burninated
- Temporary policy: ChatGPT is banned
Hot Network Questions
- BASIC line input buffer location on ZX Spectrum
- How do you get out of a corner when plotting yourself into a corner
- Checking system vs. SEPA and the like
- What is pictured in this SHERLOC camera?
- Bulk update symbol size units from mm to map units in rule-based symbology
- Acidity of alcohols and basicity of amines
- Minimising the environmental effects of my dyson brain
- What video game is Charlie playing in Poker Face S01E07?
- Do new devs get fired if they can't solve a certain bug?
- Are there tables of wastage rates for different fruit and veg?
- Why do many companies reject expired SSL certificates as bugs in bug bounties?
- Does Counterspell prevent from any further spells being cast on a given turn?
- Calculating probabilities from d6 dice pool (Degenesis rules for botches and triggers)
- How can I detect that \centering is in effect?
- Biodiversity through radiation
- How to prove that the supernatural or paranormal doesn't exist?
- QGIS - Countif function
- Is there a proper earth ground point in this switch box?
- My code is GPL licensed, can I issue a license to have my code be distributed in a specific MIT licensed project?
- Transgenic Peach DNA Splicing
- OIDC ID Token nonce as "poor mans digital signature"
- remove package from CTAN
- Why are physically impossible and logically impossible concepts considered separate in terms of probability?
- Is it possible to develop the Ubuntu kernel on the same computer that one works on?
Assigning Null Value to Nested Table Variable
- Script Name Assigning Null Value to Nested Table Variable
- Description This example initializes the nested table variable dept_names to a non-null value; assigns a null collection to it, making it null; and re-initializes it to a different non-null value.
- Area PL/SQL General
- Referenced In Database PL/SQL Language Reference
- Contributor Sarah Hirschfeld (Oracle)
- Created Thursday February 02, 2017
- Statement 1 DECLARE TYPE dnames_tab IS TABLE OF VARCHAR2(30); dept_names dnames_tab := dnames_tab( 'Shipping','Sales','Finance','Payroll'); -- Initialized to non-null value empty_set dnames_tab; -- Not initialized, therefore null PROCEDURE print_dept_names_status IS BEGIN IF dept_names IS NULL THEN DBMS_OUTPUT.PUT_LINE('dept_names is null.'); ELSE DBMS_OUTPUT.PUT_LINE('dept_names is not null.'); END IF; END print_dept_names_status; BEGIN print_dept_names_status; dept_names := empty_set; -- Assign null collection to dept_names. print_dept_names_status; dept_names := dnames_tab ( 'Shipping','Sales','Finance','Payroll'); -- Re-initialize dept_names print_dept_names_status; END; dept_names is not null. dept_names is null. dept_names is not null.
- Database on OTN SQL and PL/SQL Discussion forums Oracle Database Download Oracle Database
- HCM Data Loader
How to Set a Value to Null
To explicitly set an attribute value to null, the value you must supply is determined by the data type of the value.
Oracle SQL*Plus: The Definitive Guide, 2nd Edition by Jonathan Gennick
Get full access to Oracle SQL*Plus: The Definitive Guide, 2nd Edition and 60K+ other titles, with a free 10-day trial of O'Reilly.
There are also live events, courses curated by job role, and more.
The NULL setting changes the text SQL*Plus prints in a column when the value for that column is null.
Is the text you want to print in place of a null value.
The default null text setting is an empty string, which causes null values to print as blanks. The following example shows this and shows how the null text may be changed:
If you use the COLUMN command to format a column, the NULL clause of that command will override this setting but only for that one column.
Get Oracle SQL*Plus: The Definitive Guide, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
Don’t leave empty-handed
Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.
It’s yours, free.
Check it out now on O’Reilly
Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.
NULL is a marker that represents missing, unknown, or inapplicable data.
A column or variable of any datatype can be NULL because NULL is not of NUMBER, CHAR, or any other specific data type. Do not use NULL to represent a value of zero, because they are not equivalent.
- 1 NOT NULL constraint
- 2.1 Some invalid examples
- 2.2 Valid examples
NOT NULL constraint [ edit ]
Columns in a table can be defined as NOT NULL to indicate that they may not contain NULL values (a value must be entered). Example:
Comparisons [ edit ]
Any arithmetic expression containing a NULL always evaluates to NULL. For example, 10 + NULL = NULL. In fact, all operators (except concatenation and the DECODE function) return null when given a null operand.
Some invalid examples [ edit ]
Nothing is ever equal to a NULL not even NULL ("anything = NULL" evaluates as UNKNOWN):
returns no rows.
Nothing can be "not equal" to a NULL either ("anything <> NULL" evaluates as UNKNOWN):
returns no rows even if some rows contains a not NULL value in its COMM column.
An empty string literal is evaluated in the same way as NULL literal ("anything = " evaluates as UNKNOWN):
returns no rows even if some rows have an empty string (or NULL) in its LOC column.
Valid examples [ edit ]
The correct way to test a NULL value is to use the IS NULL or IS NOT NULL test.
Select column values that are NULL:
Select column values that are NOT NULL:
Change a column value to NULL:
Sorting [ edit ]
In ascending order, NULL values will always be sorted last and thus appear after the other data. In descending order NULL values will appear first. The sort order of NULL values can be overridden using the NULLS FIRST/LAST clause.
Also see [ edit ]
- NVL , Null value function
- Not logged in
- Blogger Home
- Blog Aggregator
- Mailing Lists
- Usenet News
- Recent changes
- Random page
- What links here
- Related changes
- Special pages
- Printable version
- Permanent link
- Page information
- This page was last edited on 6 September 2018, at 17:23.
- About Oracle Wiki
- Data Structure & Algorithm Classes (Live)
- System Design (Live)
- Explore More Live Courses
- Interview Preparation Course
- Data Science (Live)
- GATE CS & IT 2024
- Data Structure & Algorithm-Self Paced(C++/JAVA)
- Data Structures & Algorithms in Python
- Explore More Self-Paced Courses
- C++ Programming - Beginner to Advanced
- Java Programming - Beginner to Advanced
- C Programming - Beginner to Advanced
- Android App Development with Kotlin(Live)
- Full Stack Development with React & Node JS(Live)
- Java Backend Development(Live)
- React JS (Basic to Advanced)
- Complete Data Science Program(Live)
- Mastering Data Analytics
- CBSE Class 12 Computer Science
- School Guide
- All Courses
- Linked List
- Binary Tree
- Binary Search Tree
- Advanced Data Structure
- All Data Structures
- Asymptotic Analysis
- Worst, Average and Best Cases
- Asymptotic Notations
- Little o and little omega notations
- Lower and Upper Bound Theory
- Analysis of Loops
- Solving Recurrences
- Amortized Analysis
- What does 'Space Complexity' mean ?
- Pseudo-polynomial Algorithms
- Polynomial Time Approximation Scheme
- A Time Complexity Question
- Searching Algorithms
- Sorting Algorithms
- Graph Algorithms
- Pattern Searching
- Geometric Algorithms
- Bitwise Algorithms
- Randomized Algorithms
- Greedy Algorithms
- Dynamic Programming
- Divide and Conquer
- Branch and Bound
- All Algorithms
- Company Preparation
- Practice Company Questions
- Interview Experiences
- Experienced Interviews
- Internship Interviews
- Competitive Programming
- Design Patterns
- System Design Tutorial
- Multiple Choice Quizzes
- Go Language
- Tailwind CSS
- Foundation CSS
- Materialize CSS
- Semantic UI
- Angular PrimeNG
- Angular ngx Bootstrap
- jQuery Mobile
- jQuery EasyUI
- React Bootstrap
- React Rebass
- React Desktop
- React Suite
- ReactJS Evergreen
- ReactJS Reactstrap
- English Grammar
- School Programming
- Number System
- Class 8 Syllabus
- Class 9 Syllabus
- Class 10 Syllabus
- Class 8 Notes
- Class 9 Notes
- Class 10 Notes
- Class 11 Notes
- Class 12 Notes
- Class 8 Maths Solution
- Class 9 Maths Solution
- Class 10 Maths Solution
- Class 11 Maths Solution
- Class 12 Maths Solution
- Class 7 Notes
- History Class 7
- History Class 8
- History Class 9
- Geo. Class 7
- Geo. Class 8
- Geo. Class 9
- Civics Class 7
- Civics Class 8
- Business Studies (Class 11th)
- Microeconomics (Class 11th)
- Statistics for Economics (Class 11th)
- Business Studies (Class 12th)
- Accountancy (Class 12th)
- Macroeconomics (Class 12th)
- Machine Learning
- Data Science
- Operating System
- Computer Networks
- Computer Organization and Architecture
- Theory of Computation
- Compiler Design
- Digital Logic
- Software Engineering
- GATE 2024 Live Course
- GATE Computer Science Notes
- Last Minute Notes
- GATE CS Solved Papers
- GATE CS Original Papers and Official Keys
- GATE CS 2023 Syllabus
- Important Topics for GATE CS
- GATE 2023 Important Dates
- Software Design Patterns
- HTML Cheat Sheet
- CSS Cheat Sheet
- Bootstrap Cheat Sheet
- JS Cheat Sheet
- jQuery Cheat Sheet
- Angular Cheat Sheet
- Facebook SDE Sheet
- Amazon SDE Sheet
- Apple SDE Sheet
- Netflix SDE Sheet
- Google SDE Sheet
- Wipro Coding Sheet
- Infosys Coding Sheet
- TCS Coding Sheet
- Cognizant Coding Sheet
- HCL Coding Sheet
- FAANG Coding Sheet
- Love Babbar Sheet
- Mass Recruiter Sheet
- Product-Based Coding Sheet
- Company-Wise Preparation Sheet
- Array Sheet
- String Sheet
- Graph Sheet
- ISRO CS Original Papers and Official Keys
- ISRO CS Solved Papers
- ISRO CS Syllabus for Scientist/Engineer Exam
- UGC NET CS Notes Paper II
- UGC NET CS Notes Paper III
- UGC NET CS Solved Papers
- Campus Ambassador Program
- School Ambassador Program
- Geek of the Month
- Campus Geek of the Month
- Placement Course
- Student Chapter
- Geek on the Top
- Geography Notes
- History Notes
- Science & Tech. Notes
- Ethics Notes
- Polity Notes
- Economics Notes
- UPSC Previous Year Papers
- SSC CGL Syllabus
- General Studies
- Subjectwise Practice Papers
- Previous Year Papers
- SBI Clerk Syllabus
- General Awareness
- Quantitative Aptitude
- Reasoning Ability
- SBI Clerk Practice Papers
- SBI PO Syllabus
- SBI PO Practice Papers
- IBPS PO 2022 Syllabus
- English Notes
- Reasoning Notes
- Mock Question Papers
- IBPS Clerk Syllabus
- Apply for a Job
- Apply through Jobathon
- Hire through Jobathon
- All DSA Problems
- Problem of the Day
- GFG SDE Sheet
- Top 50 Array Problems
- Top 50 String Problems
- Top 50 Tree Problems
- Top 50 Graph Problems
- Top 50 DP Problems
- Solving For India-Hackthon
- GFG Weekly Coding Contest
- Job-A-Thon: Hiring Challenge
- BiWizard School Contest
- All Contests and Events
- Saved Videos
- What's New ?
- Data Structures
- Interview Preparation
- Topic-wise Practice
- Latest Blogs
- Write & Earn
- Web Development
- Write Articles
- Pick Topics to write
- Guidelines to Write
- Get Technical Writing Internship
- Write an Interview Experience
How to Set a Column Value to Null in SQL?
- SQL | NULL functions
- SQL | Numeric Functions
- SQL | String functions
- SQL | Advanced Functions
- Joining three or more tables in SQL
- How to Get the names of the table in SQL
- SQL | Sub queries in From Clause
- SQL Correlated Subqueries
- SQL | Top-N Queries
- SQL | Subquery
- How to print duplicate rows in a table?
- How to make a website using WordPress (Part – 2)
- How to make a website using WordPress (Part – 1)
- WordPress Introduction
- Step by Step guide to Write your own WordPress Template
- Step by step guide to make your first WordPress Plugin
- Making your WordPress Website More Secure
- How cookies are used in a website?
- HTTP Cookies
- Session Management in HTTP
- Session Hijacking
- Basic SQL Injection and Mitigation with Example
- SQL Interview Questions
- SQL Interview Questions | Set 1
- SQL | DDL, DQL, DML, DCL and TCL Commands
- SQL | Join (Inner, Left, Right and Full Joins)
- SQL | WITH clause
- SQL | ALTER (RENAME)
- How to find Nth highest salary from a table
- Last Updated : 21 Aug, 2021
In this article, we will look into how you can set the column value to Null in SQL.
Firstly, let’s create a table using CREATE TABLE command:
The table would look like this:
To UPDATE Column value, use the below command:
To set column value to NULL use syntax:
Example: For the above table
Column value can also be set to NULL without specifying the ‘where’ condition.
If you have set a constraint that a particular column value can not be NULL, and later try to set it as NULL, then it will generate an error.
ERROR: Gender may not be NULL.
Please Login to comment...
Improve your coding skills with practice, start your coding journey now.
Obsessed with Oracle PL/SQL
For the last twenty years, I have managed to transform an obsession with PL/SQL into a paying job. How cool is that?
Search This Blog
Pl/sql 101: nulls in pl/sql.
Null is never equal to anything else, including null. And certainly 0. Null is never not equal to anything else, including null.
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
Hello Steven, In the section "NULLs in Oracle Database" you stated that the column USER_NAME in the table cannot be set to NULL but you haven't specified a "NOT NULL" constraint on that column. Kind regards, Niels
Sheesh. Thanks. Fixed.
You might add how to "compare" NULLs. Sometimes it is necessary to treat NULL like an existing value. We have an overloaded isEqual function and we use DECODE in SQL as suggested by Tom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4410543900346652511
Thanks, I'm adding this to the Boolean Experssions with NULL section. Would you care to share your isEqual function(s)?
PACKAGE helper ... /** * @desc Check whether both values are equal or NULL * @param p_value1 date 1 * @param p_value2 date 2 * @return TRUE, if both values are equal or both values are NULL, else FALSE */ FUNCTION isEqual ( p_value1 IN DATE ,p_value2 IN DATE ) RETURN BOOLEAN IS BEGIN RETURN ( p_value1 IS NOT NULL-- Check NULL, otherwise NULL is returned! AND p_value2 IS NOT NULL AND p_value1 = p_value2 ) OR ( p_value1 IS NULL AND p_value2 IS NULL ); END isEqual; overloaded for VARCHAR and NUMBER
Hi Steven below is the my requirement could you please help on the Same Requirement: Modify the ca_custodian_iso_map_rule table using following 1.). Manage all mappings from event bridge table to source table.
bb, I am sorry but this requirement means nothing to me.
Hi Steven, in the section "Boolean Expressions with NULL" you state the following: "Notice that if x is null or y is null, then x AND y is always null." This is not the case and you show it just below in the Logical Truth Table (and even with your live sql script). TRUE AND NULL is NULL, but FALSE AND NULL is FALSE And I really can understand that behavior. In "FALSE AND NULL" we know that the final result will be "false" as one of the two boolean expressions is "false". There is no need to check the second expression, which has no defined value in this case (and so might result in a NULL result). Best regards. Jan
Jan, could you please point me to a LiveSQL script that proves the above assertion? The truth table above says x or y is NULL if x is FALSE and y is NULL. "FALSE OR NULL" is FALSE. But AND? I don't think so. Please, prove it! Thanks!
Hi Steven, the truth table says in row 6 column 3 that "FALSE AND NULL" is FALSE. And it says in the same row 6 column 4 that "FALSE OR NULL" is NULL. And your LiveSQL script already proved that. Here ist "my" prove: I just took your script, added a translation to text for the boolean values and reduced it to the cases in question: https://livesql.oracle.com/apex/livesql/s/gc9p0rsziru2vzwxf3sstmo21 Did I get something wrong? Best regards, Jan
Ha! That's what I get for answering commentary on my blog at 4:45 AM. Sorry bout that, Jan, you are right and I am wrong.
No worries! I had an advantage - I'm 9 hours ahead and already had my sleep. ;)
Post a Comment
Popular posts from this blog, minimize context switches and unnecessary pl/sql code: an example from the pl/sql challenge, get rid of mutating table trigger errors with the compound trigger, how to pick the limit for bulk collect.
Home » Oracle Basics » Oracle NOT NULL
Oracle NOT NULL
Summary : in this tutorial, you will learn how to use the Oracle NOT NULL constraint to enforce a column not to accept NULL values.
An Oracle NOT NULL constraint specifies that a column cannot contain NULL values. The Oracle NOT NULL constraints are inline constraints which are typically used in the column definition of the CREATE TABLE statement.
It is possible to add a NOT NULL constraint to an existing table by using the ALTER TABLE statement.
In this case, the column_name must not contain any NULL value before applying the NOT NULL constraint.
Oracle NOT NULL constraint examples
The following statement creates the surcharges table:
The surcharges table has three columns: surcharge id, surcharge name, and amount.
The surcharge_id column is the primary key column of the table specified by the PRIMARY KEY constraint, therefore, Oracle implicitly adds a NOT NULL constraint to this column.
The surcharge_name column has a NOT NULL constraint specified explicitly in the column definition.
The amount column can accept NULL values.
The following statement inserts a row into the surcharges table:
It works as expected.
However, the following statement does not work:
Because it attempts to insert a NULL value into the surcharge column which has a NOT NULL constraint.
The following statement works because the amount column accepts NULL values:
The following statement displays all constraints of the surcharges table:
If you want to add a NOT NULL constraint to the amount column, you use the following ALTER TABLE statement:
The following error occurred:
Because the surcharges table contains a NULL value.
So before adding the NOT NULL constraint, you need to make sure that the existing data in the surcharges table does not violate the NOT NULL constraint:
Now, if you execute the ALTER TABLE statement again:
It should work as expected.
Drop NOT NULL constraints
Sometimes, you need to change a column with a NOT NULL constraint to accept NULL values.
To do this, you need to remove the NOT NULL constraint from the column by using the ALTER TABLE statement as below:
For example, to drop the NOT NULL constraint from the amount column of the surcharges table, you use the following statement:
In this tutorial, you have learned how to use the Oracle NOT NULL constraint to enforce a column not to accept NULL values.
- Oracle / PLSQL
- Foreign Keys
- Web Development
- Color Picker
- Techie Humor
- AND & OR
- COMPARISON OPERATORS
- IS NOT NULL
- Alter Table
- Alter Tablespace
- Change Password
- Check Constraints
- Comments in SQL
- Create Schema
- Create Schema Statement
- Create Table
- Create Table As
- Create Tablespace
- Create User
- Declare Variables
- Drop Tablespace
- Error Messages
- Find Default Tablespace
- Find Users Logged In
- Find Version Information
- Global Temporary
- Grant/Revoke Privileges
- Local Temporary
- Primary Keys
- Set Default Tablespace
- System Tables
- Unique Constraints
- Close Cursor
- Cursor Attributes
- Declare Cursor
- Fetch Cursor
- Open Cursor
- Select For Update
- Where Current Of
Oracle Exception Handling
- Named Programmer-Defined Exception
- Named System Exception
- WHEN OTHERS Clause
Oracle Foreign Keys
- Disable Foreign Key
- Drop Foreign Key
- Enable Foreign Key
- Foreign Key
- Foreign Key (cascade delete)
- Foreign Key (set null delete)
- CURSOR FOR LOOP
- REPEAT UNTIL LOOP
- Commit Transaction
- Rollback Transaction
- Set Transaction
- After Delete Trigger
- After Insert Trigger
- After Update Trigger
- Before Delete Trigger
- Before Insert Trigger
- Before Update Trigger
- Disable All Triggers
- Disable Trigger
- Drop Trigger
- Enable All Triggers
- Enable Trigger
- Concat with ||
- ROUND (numbers)
- TRUNC (numbers)
- ROUND (dates)
- TRUNC (dates)
Oracle / PLSQL: Foreign Keys with Set Null on Delete
This Oracle tutorial explains how to use Foreign Keys with "set null on delete" in Oracle with syntax and examples.
What is a foreign key with "Set NULL on Delete" in Oracle?
A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to null. The records in the child table will not be deleted.
A foreign key with a "set null on delete" can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using a CREATE TABLE statement
The syntax for creating a foreign key using a CREATE TABLE statement is:
In this example, we've created a primary key on the supplier table called supplier_pk . It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.
Because of the set null on delete, when a record in the supplier table is deleted, all corresponding records in the products table will have the supplier_id values set to null.
We could also create a foreign key "set null on delete" with more than one field as in the example below:
In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.
The delete on the foreign key called fk_foreign_comp causes all corresponding records in the products table to have the supplier_id and supplier_name fields set to null when a record in the supplier table is deleted, based on supplier_id and supplier_name.
Using an ALTER TABLE statement
The syntax for creating a foreign key in an ALTER TABLE statement is:
In this example, we've created a foreign key "with a set null on delete" called fk_supplier that references the supplier table based on the supplier_id field.
We could also create a foreign key "with a set null on delete" with more than one field as in the example below:
Home | About Us | Contact Us | Testimonials | Donate
Copyright © 2003-2023 TechOnTheNet.com. All rights reserved.
- Manage Email Subscriptions
- How to Post to DZone
- Article Submission Guidelines
- Manage My Drafts
- Data Engineering
PL/SQL 101: Nulls in PL/SQL
You need to understand nulls, the null value, the null statement, and how nulls can cause confusion and errors. then, take steps to avoid that confusion and those errors..
Join the DZone community and get the full member experience.
The Oracle Database supports a concept of a null value, which means, essentially, that it has no value . The idea of having nulls in a relational database is controversial , but Oracle Database supports them and you need to know how they can impact your work in PL/SQL.
First, and most important, remember that:
Null is never equal to anything else, including null. And certainly 0. And null is never not equal to anything else, including null.
You can rest assured that the code represented by ... will never be executed.
Note : NULL in the above code is a literal value with a non-value of null.
The same holds true for where clause predicates in a SQL statement. The following queries will never return any rows, regardless of the contents of the employees table.
Of course, you will rarely write code that includes explicit references to the NULL literal. You are more likely to see and write code like this:
If a NULL value could be passed to my_proc for the value_in parameter, that inequality will never evaluate to true. That might be fine, but you need to give consideration very deliberately to these questions: "Could a null value be passed in here? Should I allow a null value to be passed here?"
Then take the necessary steps to bulletproof your code from nulls (see last section in this blog post for details).
With that, let's dive into the details.
NULLs in Oracle Database
Nulls work the same in SQL and PL/SQL and, as is the case with so much else in PL/SQL, you should apply the documented descriptions from SQL to PL/SQL unless otherwise noted, so here's the doc on Nulls in SQL .
A column's value can be null unless you defined that column with a NOT NULL constraint, as in:
When a row is inserted into and updated in this table, the following columns cannot be set to NULL :
I don't have to provide a Twitter account name.
Wait, you might be saying: Why do you have to provide a value for ID ? You didn't specify NOT NULL .
That's because identity columns can never be set to NULL . Identity columns are new to 12.1 and replace the use of sequences to generate unique values for primary keys.
Moving on to PL/SQL, a variable's value can be null unless you declared that variable with a NOT NULL constraint or use the CONSTANT keyword to define your variable as a constant. That behavior is shown in the code below.
Note that you cannot specify that a parameter in a procedure or function must be NOT NULL . Instead, you must use a datatype that cannot be null or add code to your subprogram to "protect" it from null values. See the section Bulletproofing for Nulls below.
NULLs and Empty Strings
Here's something to keep in mind:
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls .
In other words, the following block displays 'Null' twice:
But we encourage you to not write code that assumes that this will always be the case.
Hey, having said that, the Oracle Database team has a very strong history of not changing behavior in new releases that causes problems in the 1,000,000s of lines of existing SQL and PL/SQL code out there "in the wild."
Declarations: NULL's the Default!
Whenever you declare a variable, it's value is set to NULL — unless you assign a different value to it. So, yes, you could do the following:
But I suggest that you do not. You take the risk of other people reading your code and thinking "Gee, I guess Steven doesn't understand PL/SQL too well."
Instead, simply declare the variable and let the PL/SQL engine take care of the default initialization to NULL , as in:
Don't worry; Oracle is never going to change this behavior, so you don't have to "take out insurance."
Boolean Expressions With NULL
Study this Truth Table and make sure you are comfortable with everything you see in it. You can even verify it to yourself with this LiveSQL script .
Notice that if x is null or y is null, then x AND y is always null. But x OR y evaluates to TRUE if either x or y is TRUE , even if the other value is NULL .
The most important thing to remember is that in almost every case if any part of your expression evaluates to NULL , the entire expression will evaluate to NULL .
In addition, pay close attention to how you construct conditional statements and expressions, when an expression might evaluate to NULL .
Compare the following two blocks.
In Block 1, if expr evaluates to NULL , then do_that will be executed.
In Block 2, if expr evaluates to NULL , then neither do_this nor do_that will be executed.
But what if you want to treat two NULL s as being equal when you compare two values? Marcus in the comments below suggests creating an isEqual function that handles this for you. He was inspired by an AskTOM thread from years past (2012), in which Tom suggested using DECODE (available in SQL only), since it treats two NULL s as equal:
Here's the implementation of Marcus's isequal function for dates:
NULL is not just a (not) value in PL/SQL. It is also a statement , albeit a ''no-op" (no operation)-it only passes control to the next statement. Here are two examples.
I use the NULL statement inside an ELSE clause. I don't need to do this, but sometimes it's worth being explicit and letting future programmers know that you thought about the ELSE part of this IF statement and you really really don't want to do anything if expr is not TRUE ( FALSE or NULL0 ).
In this next block, I use NULL; as the "target" for a GOTO statement. Yes, that's right, PL/SQL has a GOTO statement — and you should rarely if ever need to use it. If/when you do, however, you need at least one executable statement after your label. If the point of the GOTO is simply to go to the end of the subprogram, then NULL; is the perfect statement with which to finish up.
Bulletproofing for Nulls
Null values can be a real pain. They can cause unexpected failures and exceptions in your algorithms. They can mess up queries, either returning rows you didn't want or excluding rows when you want them.
So, the first thing to do is to decide very carefully where and when you want to allow nulls. If a column's value should never be null, then define it that way. If a variable should never be set to NULL , then add the NOT NULL constraint or define it as a constant (as appropriate).
If you are writing a subprogram (procedure or function) and a parameter's value should never be null, you cannot add NOT NULL to the parameter definition. Instead, you will need to do one of the following:
Use a datatype for the parameter that cannot be null. These can be base datatypes, such as NATURALN , or subtypes. See the examples below.
Add code to your subprogram to check or assert that the actual argument value is not null.
So yes there are some base datatypes that are inherently NOT NULL -able, such as NATURALN .
And when I use that datatype for my parameter, it stops NULL s cold in their tracks.
What if you are passing a string to your procedure and there is no base VARCHAR2 datatype that excludes NULL s? Then, you declare your own subtype and make that new datatype not-nullable. Let's take a look:
Note that the exceptions raised above occur before the procedure is even executed, so the exception handler of the procedure cannot trap the exception (you do not see "Error!" after trying to run pkg.no_nulls ). That may be just what you want.
But if you cannot use a NOT NULL datatype for your parameter or you want to be able to trap the exception inside the subprogram, then you should write your own assertion code that runs right at the top of the subprogram. Here's an example:
Better yet is to use a predefined and application-standard assertion package. That way, your code could look more like this:
Don't have an assertion package? No problem! Grab mine from LiveSQL .
Avoid NULL Headaches
It really doesn't matter if you believe ever-so-strongly that Oracle (and other RDBMS vendors) should not have allowed NULL s in its relational database. If you'd like to go down that rabbit hole, start here .
NULL s are in Oracle Database and they are here to stay.
So, you should understand nulls, the NULL value, the NULL statement, and how nulls can cause confusion and errors.
Then take steps in your code to avoid that confusion and those errors.
Do everything that you can declaratively (such as defining a column as NOT NULL ).
Bullet-proof your subprograms with assertion logic to ensure that your program is free of nulls (when that is how it should be).
Published at DZone with permission of Steven Feuerstein , DZone MVB . See the original article here.
Opinions expressed by DZone contributors are their own.
Popular on DZone
- Comparing the Top 3 Schema Management Tools
- JSON in Kotlin
- 40 Best UI Testing Tools And Techniques
- Azure Observability
- About DZone
- Send feedback
- Advertise with DZone
CONTRIBUTE ON DZONE
- Become a Contributor
- Visit the Writers' Zone
- Terms of Service
- 600 Park Offices Drive
- Durham, NC 27709
- [email protected]
- +1 (919) 678-0300
Let's be friends:
If you want to use the GUI... click/double-click the table and select the Data tab. Click in the column value you want to set to (null)
Script Name Assigning Null Value to Nested Table Variable · Description This example initializes the nested table variable dept_names to a non-
To explicitly set an attribute value to null, the value you must supply is determined by the data type of the value.
Name SET NULL Synopsis The NULL setting changes the text SQL*Plus prints in a column when the value for that column is null. Syntax SET NULL null_text
NULL is a marker that represents missing, unknown, or inapplicable data. A column or variable of any datatype can be NULL because NULL is
update students set Gender = NULL where Gender='F'; SELECT * FROM students ;. Output: Column value can also be set to NULL without specifying
The Oracle Database supports a concept of a null value, which means, essentially, that it has no value. The idea of having nulls in a
An Oracle NOT NULL constraint specifies that a column cannot contain NULL values. The Oracle NOT NULL constraints are inline constraints which are typically
A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the
The Oracle Database supports a concept of a null value, which means, essentially, that it has no value. The idea of having nulls in a