named_not_null_constraint

View All Scripts Login to Run Script

Script Name named_not_null_constraint
Description How can I name a "not null" constraint that sets the NULLABLE flag in DBA/USER_TAB_COLUMNS?
Area SQL General / DDL
Contributor Don Simpson
Created Friday May 19, 2017

Statement 1

create table FOO (BAR char(1))

Table created.

Statement 2

alter table FOO add constraint FOOBAR check (BAR is not null)

Table altered.

Statement 3

select TABLE_NAME, COLUMN_NAME, NULLABLE from USER_TAB_COLUMNS where TABLE_NAME = 'FOO'


TABLE_NAME COLUMN_NAME NULLABLE
FOO BAR Y

TABLE_NAME	COLUMN_NAME	NULLABLE
FOO	BAR	Y

Statement 4

select TABLE_NAME, CONSTRAINT_NAME, SEARCH_CONDITION from USER_CONSTRAINTS where TABLE_NAME = 'FOO'


TABLE_NAME CONSTRAINT_NAME SEARCH_CONDITION
FOO FOOBAR BAR is not null

TABLE_NAME	CONSTRAINT_NAME	SEARCH_CONDITION
FOO	FOOBAR	BAR is not null

Statement 5

alter table FOO drop constraint FOOBAR

Table altered.

Statement 6

alter table FOO modify BAR not null

Table altered.

Statement 7

select TABLE_NAME, COLUMN_NAME, NULLABLE from USER_TAB_COLUMNS where TABLE_NAME = 'FOO'


TABLE_NAME COLUMN_NAME NULLABLE
FOO BAR N

TABLE_NAME	COLUMN_NAME	NULLABLE
FOO	BAR	N

Statement 8

select TABLE_NAME, CONSTRAINT_NAME, SEARCH_CONDITION from USER_CONSTRAINTS where TABLE_NAME = 'FOO'


TABLE_NAME CONSTRAINT_NAME SEARCH_CONDITION
FOO SYS_C002822781 "BAR" IS NOT NULL

TABLE_NAME	CONSTRAINT_NAME	SEARCH_CONDITION
FOO	SYS_C002822781	"BAR" IS NOT NULL

Statement 10

create table FOO (BAR char(1))

Table created.

Statement 11

alter table FOO add constraint FOOBAR check (BAR is not null)

Table altered.

Statement 12

select TABLE_NAME, CONSTRAINT_NAME, SEARCH_CONDITION from USER_CONSTRAINTS where TABLE_NAME = 'FOO'


TABLE_NAME CONSTRAINT_NAME SEARCH_CONDITION
FOO FOOBAR BAR is not null

TABLE_NAME	CONSTRAINT_NAME	SEARCH_CONDITION
FOO	FOOBAR	BAR is not null

Statement 13

select TABLE_NAME, COLUMN_NAME, NULLABLE from USER_TAB_COLUMNS where TABLE_NAME = 'FOO'


TABLE_NAME COLUMN_NAME NULLABLE
FOO BAR Y

TABLE_NAME	COLUMN_NAME	NULLABLE
FOO	BAR	Y

Statement 14

alter table FOO drop constraint FOOBAR

Table altered.

Statement 15

alter table FOO modify BAR not null

Table altered.

Statement 16

select TABLE_NAME, CONSTRAINT_NAME, SEARCH_CONDITION from USER_CONSTRAINTS where TABLE_NAME = 'FOO'


TABLE_NAME CONSTRAINT_NAME SEARCH_CONDITION
FOO SYS_C002822783 "BAR" IS NOT NULL

TABLE_NAME	CONSTRAINT_NAME	SEARCH_CONDITION
FOO	SYS_C002822783	"BAR" IS NOT NULL

Statement 17

select TABLE_NAME, COLUMN_NAME, NULLABLE from USER_TAB_COLUMNS where TABLE_NAME = 'FOO'


TABLE_NAME COLUMN_NAME NULLABLE
FOO BAR N

TABLE_NAME	COLUMN_NAME	NULLABLE
FOO	BAR	N

Additional Information