Relationship between constraints and column attributes NULLABLE in Oracle 10 GB

Relationship between constraints and column attributes NULLABLE in Oracle 10 GB

Conclusion:

The columname type not null and check (columnname is not null) results are different.

Therefore:

1. You do not need to manually match the NULLABLE attribute. When all constraints explicitly causing NULLABLE to be changed from Y to N are deleted, NULLABLE is naturally restored to Y.

2. Do NOT use CHECK to implement not null. You can use MODIFY or directly declare it after the field.

Drop table zwxtest04;
Create table zwxtest04
(

Id integer
);
Alter table zwxtest04 add constraint zwxtest04c2 check (id is not null );
Select * from user_tab_columns where table_name = 'zwxtest04 ';
Select * from user_constraints where table_name = 'zwxtest04 ';

-- NULLABLE is Y, and the constraint does not cause NULLABLE changes.

Drop table zwxtest04;
Create table zwxtest04
(

Id integer not null
);

Select * from user_tab_columns where table_name = 'zwxtest04 ';
Select * from user_constraints where table_name = 'zwxtest04 ';

-- NULLABLE is N, and a C-type not null constraint is automatically added.

Drop table zwxtest04;
Create table zwxtest04
(

Id integer

);
Alter table zwxtest04 id not nul;
Select * from user_tab_columns where table_name = 'zwxtest04 ';
Select * from user_constraints where table_name = 'zwxtest04 ';

-- NULLABLE is N, and a C-type not null constraint is automatically added.

Drop table zwxtest04;
Create table zwxtest04
(

Id integer
);
Alter table zwxtest04 add constraint zwxtest04c3 primary key (id );
Select * from user_tab_columns where table_name = 'zwxtest04 ';
Select * from user_constraints where table_name = 'zwxtest04 ';

-- NULLABLE is N. Create a P-type constraint and a UNIQUE index.

Alter table zwxtest04 drop constraint zwxtest04c3;

-- NULLABLE is Y