PRIMARY KEY
(constraint type: P
) - Ensures each row is uniquely identifiable. Automatically creates a unique index and enforces NOT NULL on all key columns.FOREIGN KEY
(constraint type: R
) - Maintains referential integrity between tables by ensuring values match those in the referenced table's primary or unique key.UNIQUE
(constraint type: U
) - Prevents duplicate values in specified columns while allowing NULL values. Automatically creates a unique index.CHECK
(constraint type: C
) - Validates data based on specified conditions. Also includes NOT NULL constraints which are implemented as CHECK constraints internally.CREATE TABLE table_constraint_1 ( id NUMBER(1,0) NOT NULL -- NOT NULL , CONSTRAINT cp_table_constraint_1_id PRIMARY KEY (id) -- PRIMARY KEY , CONSTRAINT cc_table_constraint_1_id CHECK (id > 0 AND id < 100) -- CHECK );
CREATE TABLE table_constraint_2 ( id NUMBER(1,0) , CONSTRAINT cr_table_constraint_2_id FOREIGN KEY (id) REFERENCES table_constraint_1 (id) -- FOREIGN KEY , CONSTRAINT cu_table_constraint_2_id UNIQUE (id) -- UNIQUE );
ALTER TABLE table_constraint_2 ADD CONSTRAINT cc_table_constraint_2_id CHECK (id IS NOT NULL); -- CHECK
USER_CONSTRAINTS
data dictionary view to retrieve information about constraints.
Key columns include CONSTRAINT_NAME, CONSTRAINT_TYPE, GENERATED (shows if name was user-defined or system-generated), SEARCH_CONDITION (for CHECK constraints), and INDEX_NAME (for constraints with associated indexes).table_constraint_1
":SELECT constraint_name, constraint_type, generated, search_condition, index_name FROM user_constraints WHERE table_name = 'TABLE_CONSTRAINT_1';Query result:
+--------------------------+-----------------+----------------+---------------------+--------------------------+ | CONSTRAINT_NAME | CONSTRAINT_TYPE | GENERATED | SEARCH_CONDITION | INDEX_NAME | +--------------------------+-----------------+----------------+---------------------+--------------------------+ | SYS_C007078 | C | GENERATED NAME | "ID" IS NOT NULL | (null) | | CC_TABLE_CONSTRAINT_1_ID | C | USER NAME | ID > 0 AND ID < 100 | (null) | | CP_TABLE_CONSTRAINT_1_ID | P | USER NAME | (null) | CP_TABLE_CONSTRAINT_1_ID | +--------------------------+-----------------+----------------+---------------------+--------------------------+Note: The SYS_C007078 constraint was automatically created for the NOT NULL declaration. Oracle generates system names starting with "SYS_C" when constraints are not explicitly named.
table_constraint_2
":SELECT constraint_name, constraint_type, generated, search_condition, index_name FROM user_constraints WHERE table_name = 'TABLE_CONSTRAINT_2';Query result:
+--------------------------+-----------------+----------------+---------------------+--------------------------+ | CONSTRAINT_NAME | CONSTRAINT_TYPE | GENERATED | SEARCH_CONDITION | INDEX_NAME | +--------------------------+-----------------+----------------+---------------------+--------------------------+ | CU_TABLE_CONSTRAINT_2_ID | U | USER NAME | (null) | CU_TABLE_CONSTRAINT_2_ID | | CR_TABLE_CONSTRAINT_2_ID | R | USER NAME | (null) | (null) | | CC_TABLE_CONSTRAINT_2_ID | C | USER NAME | ID IS NOT NULL | (null) | +--------------------------+-----------------+----------------+---------------------+--------------------------+Additional Views: Use
USER_CONS_COLUMNS
to see which columns are involved in each constraint, and ALL_CONSTRAINTS
or DBA_CONSTRAINTS
to view constraints across schemas.ALTER TABLE table_constraint_2 DISABLE CONSTRAINT cc_table_constraint_2_id;Important: Disabling a PRIMARY KEY or UNIQUE constraint also drops the associated index. Use DISABLE NOVALIDATE to keep the index while disabling constraint enforcement.
ALTER TABLE table_constraint_2 ENABLE CONSTRAINT cc_table_constraint_2_id;Note: If existing data violates the constraint, the ENABLE operation will fail. Use ENABLE NOVALIDATE to enable the constraint without checking existing data, or fix the data first.
ALTER TABLE table_constraint_2 DROP CONSTRAINT cc_table_constraint_2_id;Note: When dropping PRIMARY KEY constraints referenced by foreign keys, use CASCADE to automatically drop dependent foreign key constraints, or drop the foreign keys first.