SQL UNION to keep Column key not null

Dear Experts,

 I have a UNION between two tables, both of them with key NOT NULL.

As Result of the union, I got the new view with Allow Null value.

Table 1:
ID Number       NOT NULL
Description VARCHAR(20)

Table 2:
ID Number NOT NULL
TITLE       VARCHAR(20)

The Union
Select ID, Description FROM Table 1
UNION
Select ID, Title FROM Table 2

Then I Check the ID Column in the Union view, I see it can be NULL instead of NOT NULL

How do I permit the view to include the Allow null for ID column?

Thanks
Jamil MuammarAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
Ah, i run it on a SQL Server. As far as I know, this is not possible in Oracle by definition when UNION [ALL] is involved.
0
 
ste5anSenior DeveloperCommented:
Your questions makes no sense. Or is there some information missing?

Your view is not updatable, so nullability does not matter.

E.g.
USE tempdb;
GO

CREATE TABLE t1
    (
        id INT NOT NULL ,
        payload INT NOT NULL
    );
CREATE TABLE t2
    (
        id INT NOT NULL ,
        payload INT NOT NULL
    );
GO

CREATE VIEW v1
AS
    SELECT *
    FROM   dbo.t1
    UNION
    SELECT *
    FROM   dbo.t2;
GO

INSERT INTO dbo.t1 ( id ,
                     payload )
VALUES ( 1, 1 ) ,
       ( 2, 2 );

INSERT INTO dbo.t2 ( id ,
                     payload )
VALUES ( 2, 3 ) ,
       ( 3, 4 );

SELECT *
FROM   v1;

INSERT INTO v1 ( id ,
                 payload )
VALUES ( 5, 5 );
GO

DROP VIEW v1;
DROP TABLE t1;
DROP TABLE t2;
GO

Open in new window


The views ID column is NOT NULL.
0
 
Jamil MuammarAuthor Commented:
Hello ste5an,

 I followed your steps, however, I got in V1, ID is Nullable.

Attached snapshot

You Can See in ID row, NULLABLE = YES :(

The needed is NULLABLE = NO

Thanks
View11.jpg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.