CODEWITHSUNDEEP
sqloracle-databaseddl
JonathanSK
JonathanSK

Reputation: 91

Create Table with check constraints

Could someone please explain how the results would differ (what are the benefits) between the two CREATE table statements?

Option #1

 CREATE TABLE sch.address_type (
  address_type_cd VARCHAR2(50 BYTE) NOT NULL CONSTRAINT sch_2002 CHECK ("address_type_cd" IS NOT NULL),
  desc_txt VARCHAR2(100 BYTE) NOT NULL CONSTRAINT sch_2003 CHECK ("DESC_TXT" IS NOT NULL),
  rec_creat_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL CONSTRAINT sch_2001 CHECK ("REC_CREAT_TS" IS NOT NULL),
  CONSTRAINT pk_address_type PRIMARY KEY (address_type_cd)
);

Option #2

CREATE TABLE sch.address_type (
  address_type_cd VARCHAR2(50 BYTE) NOT NULL,
  desc_txt VARCHAR2(100 BYTE) NOT NULL,
  rec_creat_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  CONSTRAINT pk_address_type PRIMARY KEY (address_type_cd)
);

Thanks

Upvotes: 0

Views: 885

Answers (2)

MT0
MT0

Reputation: 167832

Looking at the constraints created using the query:

SELECT constraint_name, constraint_type, search_condition
FROM ALL_CONSTRAINTS
WHERE table_name = 'ADDRESS_TYPE';

Option 1:

CONSTRAINT_NAME      C SEARCH_CONDITION             
-------------------- - ------------------------------
PK_ADDRESS_TYPE      P 
SYS_C0010366         C "ADDRESS_TYPE_CD" IS NOT NULL
SYS_C0010367         C "DESC_TXT" IS NOT NULL
SYS_C0010368         C "REC_CREAT_TS" IS NOT NULL
SCH_2002             C address_type_cd IS NOT NULL
SCH_2003             C "DESC_TXT" IS NOT NULL
SCH_2001             C "REC_CREAT_TS" IS NOT NULL

Option 2:

CONSTRAINT_NAME      C SEARCH_CONDITION             
-------------------- - ------------------------------
PK_ADDRESS_TYPE      P 
SYS_C0010373         C "ADDRESS_TYPE_CD" IS NOT NULL
SYS_C0010374         C "DESC_TXT" IS NOT NULL
SYS_C0010375         C "REC_CREAT_TS" IS NOT NULL

Option 3:

CREATE TABLE address_type (
  address_type_cd VARCHAR2(50 BYTE)  CONSTRAINT address_type__cd__pk PRIMARY KEY,
  desc_txt        VARCHAR2(100 BYTE) CONSTRAINT address_type__desc__nn NOT NULL,
  rec_creat_ts    TIMESTAMP          DEFAULT CURRENT_TIMESTAMP
                                     CONSTRAINT address_type__rct__nn NOT NULL
);

Has the constraints:

CONSTRAINT_NAME           C SEARCH_CONDITION             
------------------------- - ------------------------------
ADDRESS_TYPE__CD__PK      P 
ADDRESS_TYPE__DESC__NN    C "DESC_TXT" IS NOT NULL
ADDRESS_TYPE__RCT__NN     C "REC_CREAT_TS" IS NOT NULL

So:

  • Option 1 creates 7 constraints of which there are 3 pairs of duplicate constraints (where each pair has a user-named and a system-named constraint)
  • Option 2 creates 4 constraints (getting rid of the duplicate user-named constraints, leaving the system named constraints)
  • Option 3 creates only 3 constraints - a NOT NULL constraint is unnecessary on a PRIMARY KEY column and it also names the NOT NULL constraints.

All the options will give the same effective result. Naming your constraints is useful during debugging (even the NOT NULL constraints)

Upvotes: 0

sql_dummy
sql_dummy

Reputation: 745

Both statements do the same function, but in first query you are doing unnecessary effort. i.e address_type_cd VARCHAR2(50 BYTE) NOT NULL is same as CONSTRAINT sch_2002 CHECK ("address_type_cd" IS NOT NULL), . Both of them mean there should a value in the field and cant be equal to NULL

Upvotes: 1

Related Questions