Create and maintain field constraints for Oracle databases

Field constraints for creating an Oracle database: non-null constraints: unique constraints on Field Values constraints default foreign key constraints createtabletab_class (class_idnumberpr

Field constraints for creating an Oracle database: non-empty constraints unique constraints on Field Values default foreign key constraints create table tab_class (class_id number pr

Field constraints for creating an Oracle database:

Non-empty Constraint
Unique Constraint
Constraints on Field Values
Default Value
Foreign key constraint

Create table tab_class (
Class_id number primary key,
Class_name varchar2 (10) not null unique
);

Create table tab_stu (
Stu_id number,
-- Student name, which cannot be blank or repeated
Stu_name varchar2 (20) not null unique,
-- The Student name can only be male or female
Stu_gender varchar2 (6) not null check (stu_gender = 'male' or stu_gender = 'female '),
-- The student age must be between 18 and 60
Stu_age number check (stu_age> 18 and stu_age <60 ),
-- The email address can be left blank and cannot be the same
Stu_email varchar2 (30) unique,
Stu_address varchar2 (30 ),
-- Foreign key constraint
Class_id number not null references tab_class (class_id)
);

Maintain the created constraints:

You can add or delete constraints, but cannot directly modify them.
Enable and disable constraints.
Non-null constraints must be added using the MODIFY clause.
Add a primary key constraint to the table:

-- Maintenance Constraints
-- Create constraints
Create table tab_check (
Che_id number,
Che_name varchar2 (20)
);
-- Add primary key constraints to the table
Alter table tab_check
Add constraints tab_check primary key (che_id );

-- Add a unique constraint. tab_check_unique indicates the constraint name.
Alter table tab_check
Add constraints tab_check_unique unique (che_name );

Add check constraints:

-- Add a field
Alter table tab_check
Add che_age number;
-- Add check Constraints
Alter table tab_check
Add constraints tab_check_age check (che_age> 18 and che_age <60 );

-- Delete the primary key constraint
Alter table tab_check
Drop constraints tab_check;

-- Disable Constraints
Alter table tab_check disable constraints tab_check;

-- Enable constraints
Alter table tab_check enable constraints tab_check;

Composite constraint, joint primary key, that is, the combination of two fields into a primary key

-- Joint primary key
Create table tab_person (
Tab_firstname varchar2 (10 ),
Tab_lastname varchar2 (10 ),
Tab_gender varchar2 (5 ),
Primary key (tab_firstname, tab_lastname)
);

Add a foreign key constraint to the table:

Alter table tab_stu
Add constraints tab_stu foreign key (class_id) references tab_class (class_id );

Related reading:

Oracle integrity constraints

Oracle constraints and Indexes

From Oracle constraints to Indexes

Common Oracle Data Types and integrity constraints

ORA-02291: violation of complete constraints ...... -The parent keyword is not found.

,