How to Make a View Column Not Null

How to make a view column NOT NULL

You can achieve what you want by re-arranging your query a bit. The trick is that the ISNULL has to be on the outside before SQL Server will understand that the resulting value can never be NULL.

SELECT ISNULL(CAST(
    CASE Status
        WHEN 3 THEN 1  
        ELSE 0  
    END AS bit), 0) AS HasStatus  
FROM dbo.Product  

One reason I actually find this useful is when using an ORM and you do not want the resulting value mapped to a nullable type. It can make things easier all around if your application sees the value as never possibly being null. Then you don't have to write code to handle null exceptions, etc.

How to create a not null column in a view

You can't add a not null or check constraint to a view; see this and on the same page 'Restrictions on NOT NULL Constraints' and 'Restrictions on Check Constraints'. You can add a with check option (against a redundant where clause) to the view but that won't be marked as not null in the data dictionary.

The only way I can think to get this effect is, if you're on 11g, to add the cast value as a virtual column on the table, and (if it's still needed) create the view against that:

ALTER TABLE "MyTable" ADD "MyBDColumn" AS
    (CAST("MyColumn" AS BINARY_DOUBLE)) NOT NULL;

CREATE OR REPLACE VIEW "MyView" AS
SELECT
    "MyBDColumn" AS "MyColumn"
FROM "MyTable";

desc "MyView"

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MyColumn                                  NOT NULL BINARY_DOUBLE

---

Since you said in a comment on dba.se that this is for mocking something up, you could use a normal column and a trigger to simulate the virtual column:

CREATE TABLE "MyTable" 
(
  "MyColumn" NUMBER NOT NULL,
  "MyBDColumn" BINARY_DOUBLE NOT NULL
);

CREATE TRIGGER "MyTrigger" before update or insert on "MyTable"
FOR EACH ROW
BEGIN
    :new."MyBDColumn" := :new."MyColumn";
END;
/

CREATE VIEW "MyView" AS
SELECT
    "MyBDColumn" AS "MyColumn"
FROM "MyTable";

INSERT INTO "MyTable" ("MyColumn") values (2);

SELECT * FROM "MyView";

  MyColumn
----------
  2.0E+000

And desc "MyView" still gives:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MyColumn                                  NOT NULL BINARY_DOUBLE

As Leigh mentioned (also on dba.se), if you did want to insert/update the view you could use an instead of trigger, with the VC or fake version.

How to indicate a columns as NOT NULL in a view?

I don't know if it's worth the effort, but I think creating a function that returns Temporary Table with NOT NULL fields should work for the computed value. ex.

Create function fnSTestample()
returns @Test TABLE 
(
    tableId varchar(100) not null
)
WITH SCHEMABINDING
as
begin
    insert @Test(tableID)
    select 'some computed value' from dbo.someTable
return
end

and then you select that function in the view.

Cheers.

Create materialized view with NOT NULL column? (dblink to external table)

I made the mistake of assuming that alter table wouldn't work on an MV.

But it totally works.

alter table my_primary_sys.wo_mv modify objectid not null;

That was easy.

How to enforce NOT NULL in a view's computed column

ColumnThree will never be null if the source of the Cast is itself never null. However, that does not mean you will not get an exception if ColumnOne cannot be cast to decimal(2,0) and you will not know whether you will get an exception until you query against the view. You should consider adding an additional check to determine whether the cast will fail and help mitigate the possibility of a cast error:

Alter View dbo.ViewOne
As
Select ColumnOne, ColumnTwo
    , Case
        When IsNumeric( ColumnOne ) = 0 Then 0
        Else Cast( ColumnOne As decimal(2,0) )
        End As ColumnThree

SQL view infers nullable column from non-null table?

The below explanation is for computed columns in a table. I imagine the same applies to computed columns in a view.

The Database Engine automatically
determines the nullability of computed
columns based on the expressions used.
The result of most expressions is
considered nullable even if only
nonnullable columns are present,
because possible underflows or
overflows will produce null results as
well. Use the COLUMNPROPERTY function
with the AllowsNull property to
investigate the nullability of any
computed column in a table. An
expression that is nullable can be
turned into a nonnullable one by
specifying ISNULL(check_expression,
constant), where the constant is a
nonnull value substituted for any null
result.

An example where your expression could return NULL is

SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;

WITH Product(Quantity,StatusId) As
(
SELECT -2147483648,1
)
SELECT (CASE WHEN P.StatusId IN (5, 8) THEN 0 ELSE -P.Quantity END) AS Quantity  
FROM Product P

MySQL views where a column is NULL

I'm not sure about your left join in the query. A left join gives all rows from the left table (table1) including those for which there's no counterpart in the other table (table2). For these unmatched rows from the right table you will get NULL for all table2's columns, including table2.num.

Perhaps you are looking for an inner join. It depends on your data and whether your table2.num is NULLable. To just replace the NULLs with zeroes, use either COALESCE

CREATE VIEW `instock` AS
SELECT 
    table1.name AS name,
    table1.supl AS supply,
    COALESCE(table2.num,0) AS numbers,
    table1.maxnum AS maxnumbers
FROM
    (table1
    LEFT JOIN table2 ON ((table1.id = table2.id)))
ORDER BY table1.name

Or IFNULL

CREATE VIEW `instock` AS
SELECT 
    table1.name AS name,
    table1.supl AS supply,
    IFNULL(table2.num,0) AS numbers,
    table1.maxnum AS maxnumbers
FROM
    (table1
    LEFT JOIN table2 ON ((table1.id = table2.id)))
ORDER BY table1.name

If you want to completely skip items from table2 that are not in table1 (wrt. the ID field), you could use an inner join:

CREATE VIEW `instock` AS
SELECT 
    table1.name AS name,
    table1.supl AS supply,
    table2.num AS numbers,
    table1.maxnum AS maxnumbers
FROM
    (table1
    INNER JOIN table2 ON ((table1.id = table2.id)))
ORDER BY table1.name

Again: It depends on your needs. IFNULL/COALESCE will show 0 instead of NULL, INNER JOIN will skip the lines completely.

(When in doubt, I always refer to this explanation on joins. It's worth a mint.)

---

Related Topics