Five constraints of Oracle

OracleFive constraints

1.NOT NULL constraint: The defined column cannot be null;

For example: Change the bookname field in the created table BOOK to not be empty:

Use ALTER TABLE…MODIFY…NOT NULL;

ALTER TABLE BOOK MODIFY bookname NOT NULL;

• 1

AI writing code

2. Primary key (PRIMARY KEY) constraint: uniquely identify each row in the table;

For example: Add the primary key with the name "BOOK_PK" to the bookid field in the already created table book

Use ALTER TABLE … ADD CONSTRAINT BOOK_PK PRIMARY KEY(field);

ALTER TABLE BOOK ADD CONSTRAINT BOOK_PK PRIMARY KEY(bookid);

• 1

AI writing code

3. Unique (UNIQUE) constraint: The value of each column field cannot be the same;

For example: Add unique constraints to the phone field in the table usertable

Use ALTER TABLE … ADD CONSTRAINT BOOK_PK PRIMARY KEY(field);

ALTER TABLE usertable ADD CONSTRAINT PHONE_UK unique (phone);

• 1

AI writing code

4. FOREIGN KEY constraint: used to maintain reference integrity between slave table and primary table；

For example, dept is the primary table, emp is the slave table (foreign key table), the foreign key column in emp deptno refers to the primary key in dept

ALTER TABLE emp ADD CONSTRAINT EMP_TEPTNO_FK FOREIGN KEY(deptno) REFERENCES dept(deptno);
Cascading deletion: When deleting the referenced column in the reference table (main table), the corresponding data in the foreign key table will also be deleted (the implementation method is as follows)

ALTER TABLE emp ADD CONSTRAINT EMP_TEPTNO_FK FOREIGN KEY(deptno) REFERENCES dept ON DELETE CASCADE;

• 1

AI writing code

5. Conditional (CHECK) constraint: Each row in the table must meet this constraint.

For example: Create a student table (Student) and define a disabled state for the age column (Age).CheckConstraint (Age>0 and Age<120) (part of the codes when creating tables are as follows:)

Age int constraint AGE_CK check(Age>0 and Age<120) disable,

• 1

AI writing code