You are here: Foswiki>Service Web>CreateTable (14 Nov 2016, MezianeKettou)Edit Attach
-- MezianeKettou - 11 Nov 2016
  • A Primary Key is in itself NOT NULL
Primary Keys are per se NOT NULL, so we don't need to add a CHECK NOT NULL constraint. Look at this example:
CREATE TABLE TEST_TABLE (COL1 NUMBER PRIMARY KEY, COL2 NUMBER, COL3 NUMBER NOT NULL);
SELECT TABLE_NAME, COLUMN_NAME, NULLABLE FROM USER_TAB_COLS WHERE TABLE_NAME ='TEST_TABLE'; :

TABLE_NAME COLUMN_NAME NULLABLE
TEST_TABLE COL1 N
TEST_TABLE COL2 Y
TEST_TABLE COL3 N






  • Use the correct script to avoid the generation of 'CHECK NOT NULL' constraints with ugly names
Let's use different scripts to create tables with the same structure and a primary key based on the same column:
  1. Script Nr. 1: create table test_table_1 (id number not null primary key, col1 number not null, col2 number);
  2. Script Nr. 2: create table test_table_2 (id number primary key, col1 number not null, col2 number);
  3. Script Nr. 3: create table test_table_3 (id number, col1 number not null, col2 number, constraint test_table_3_pk primary key (id));
  4. Script Nr. 4: create table test_table_4 (id number not null, col1 number not null, col2 number, constraint test_table_4_pk primary key (id));
Let's now see how Oracle interprets and processes these scripts:
SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, GENERATED FROM SYS.ALL_CONSTRAINTS WHERE TABLE_NAME LIKE 'TEST_TABELE%';

 
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION GENERATED
TEST_TABLE_1 SYS_C001290138 C "ID" IS NOT NULL GENERATED NAME
TEST_TABLE_1 SYS_C001290139 C "COL1" IS NOT NULL GENERATED NAME
TEST_TABLE_1 SYS_C001290140 P (null) GENERATED NAME
TEST_TABLE_2 SYS_C001290141 C "COL1" IS NOT NULL GENERATED NAME
TEST_TABLE_2 SYS_C001290142 P (null) GENERATED NAME
TEST_TABLE_3 SYS_C001290143 C "COL1" IS NOT NULL GENERATED NAME
TEST_TABLE_3 TEST_TABLE_3_PK P (nul) USER NAME
TEST_TABLE_4 SYS_C001289381 C "COL1" IS NOT NULL GENERATED NAME
TEST_TABLE_4 SYS_C001289380 C "ID" IS NOT NULL GENERATED NAME
TEST_TABLE_4 TEST_TABLE_4_PK P (null) USER NAME




















 
So it becomes abvious, that:
* The CHECK NOT NULL constraint is not necessary for a Primary Key.
  • If we specify NOT NULL in the definition of a column, Oracle automatically generates a CHECK constraint (like for COL1 in all scripts) with an ugly name, even if the constraint is superfluous (like in Script Nr. 1 and Script Nr. 4 )
  • To give a Primary Key constraint a meaningful name, the explicite CONSTRAINT must be used (like in Script Nr. 3 and Script Nr. 4 )
  • To declare a column as NOT NULL, the use of the explicit CONSTRAINT is indispensable.
So the best script to create a similar table to the table above:

create table test_table (id number, col1 number, col2 number, constraint test_table_4_pk primary key (id), constraint COL1_NN NOT NULL);

or

create table test_table (id number, col1 number, col2 number);

alter table test_table add constraint test_table_4_pk primary key (id);

alter table add constraint COL1_NN NOT NULL;
Topic revision: r4 - 14 Nov 2016, MezianeKettou
This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback