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 );
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.