当前位置:网站首页>Detailed explanation of constraints of Oracle table
Detailed explanation of constraints of Oracle table
2022-04-23 13:40:00 【Wangcai 2】
Constraint Constraint name
If you do not specify a name for the constraint , that oracle The constraint is automatically named
Table level constraints and column level constraints
Scope of action :
① Column level constraints can only act on one column
② Table level constraints can act on multiple columns ( Of course, table level constraints can also act on a column )
Define the way : The column constraint must follow the definition of the column , Table constraints do not work with columns , It is defined separately .
Non empty (not null) Constraints can only be defined on columns
NOT NULL Non empty constraint
Can only be defined at the column level :
CREATE TABLE test(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
hire_date DATE CONSTRAINT test_last_name NOT NULL
)
UNIQUE Data uniqueness constraint
Be careful : Unique constraint , Multiple null values are allowed :NULL.
It can be defined at table level or column level :
( Pay attention to the separation of commas )
CREATE TABLE test(
employee_id NUMBER(6),
last_name VARCHAR2(25) UNIQUE,
email VARCHAR2(25),
hire_date DATE NOT NULL,
CONSTRAINT test_email_uk UNIQUE(email)
);
PRIMARY KEY Primary key constraint
It can be defined at table level or column level :
(1) Data cannot be empty
(2) Data should be unique
CREATE TABLE test(
employee_id NUMBER(6) PRIMARY KEY,
last_name VARCHAR2(25) UNIQUE,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
CONSTRAINT test_email_uk UNIQUE(email)
);
-- Or, CONSTRAINT test_id_pk PRIMARY KEY(department_id)
FOREIGN KEY Foreign key constraints
It can be defined at table level or column level :
Only the primary key or unique key in the parent table can be referenced as a foreign key by other tables
CREATE TABLE test(
employee_id NUMBER(6) PRIMARY KEY,
last_name VARCHAR2(25) UNIQUE,
email VARCHAR2(25),
salary NUMBER(8,2),
department_id NUMBER(4) ,
hire_date DATE NOT NULL,
CONSTRAINT test_fk FOREIGN KEY (department_id) REFERENCES departments(department_id),
CONSTRAINT test_email_uk UNIQUE(email)
);
FOREIGN KEY Keywords for constraints
FOREIGN KEY: Specify the columns in the child table at the table level
REFERENCES: Columns marked in the parent table
ON DELETE CASCADE( cascading deletion ): When the columns in the parent table are deleted , The corresponding columns in the sub table are also deleted
ON DELETE SET NULL( Cascade empty ): The corresponding column in the sub table is empty
1.ON DELETE CASCADE
If you remove departments Primary Key department_id, So as test Foreign key department_id The data in the column is deleted together
2.ON DELETE SET NULL
If you remove departments Primary Key department_id, So as test Foreign key department_id The location is empty
CREATE TABLE test(
employee_id NUMBER(6) PRIMARY KEY,
last_name VARCHAR2(25) UNIQUE,
email VARCHAR2(25),
salary NUMBER(8,2),
department_id NUMBER(4) ,
hire_date DATE NOT NULL,
CONSTRAINT test_fk FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE CONSTRAINT test_email_uk UNIQUE(email)
);
CHECK Check each row constraint
Define the conditions that each line must meet
CREATE TABLE test(
employee_id NUMBER(6) PRIMARY KEY,
last_name VARCHAR2(25) UNIQUE,
email VARCHAR2(25),
salary NUMBER(8,2),
CONSTRAINT test_salary_min CHECK (salary > 0)
);
Syntax for adding constraints
Use ALTER TABLE sentence : Add or remove constraints , But you can't modify the constraint , Valid or invalid constraints ;
Use MODIFY sentence : add to NOT NULL constraint ;
ADD CONSTRAINT Adding constraints
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
DROP CONSTRAINT Delete constraints
ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;
DISABLE CONSTRAINT Invalidate constraints
ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk;
ENABLE CONSTRAINT Activate constraint
When defining or activating UNIQUE or PRIMARY KEY Constraints are created automatically UNIQUE or PRIMARY KEY Indexes
ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk;
USER_CONSTRAINTS Query constraints
SELECT constraint_name, constraint_type,search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
USER_CONS_COLUMNS Query the columns that define constraints
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';
Such as ,
————————————————
Link to the original text :https://blog.csdn.net/weixin_42193004/article/details/89043148
版权声明
本文为[Wangcai 2]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204230604301397.html
边栏推荐
- [point cloud series] deepmapping: unsupervised map estimation from multiple point clouds
- Stack protector under armcc / GCC
- SPI NAND flash summary
- [barycentric coordinate interpolation, perspective correction interpolation] principle and usage opinions
- 【视频】线性回归中的贝叶斯推断与R语言预测工人工资数据|数据分享
- Why do you need to learn container technology to engage in cloud native development
- 这个SQL语名是什么意思
- Example interview | sun Guanghao: College Club grows and starts a business with me
- MySQL5. 5 installation tutorial
- GDB的使用
猜你喜欢
[point cloud series] learning representations and generative models for 3D point clouds
Vscode tips
Imx6ull QEMU bare metal tutorial 1: GPIO, iomux, I2C
Loading and using image classification dataset fashion MNIST in pytorch
LeetCode_ DFS_ Medium_ 695. Maximum area of the island
联想拯救者Y9000X 2020
9419页最新一线互联网Android面试题解析大全
[Journal Conference Series] IEEE series template download guide
CSDN高校俱乐部“名师高校行”——湖南师范大学站
面试官给我挖坑:URI中的 “//” 有什么用?
随机推荐
Example of specific method for TIA to trigger interrupt ob40 based on high-speed counter to realize fixed-point machining action
Logstash数据处理服务的输入插件Input常见类型以及基本使用
【官宣】长沙软件人才实训基地成立!
十万大学生都已成为猿粉,你还在等什么?
Use of GDB
Example interview | sun Guanghao: College Club grows and starts a business with me
You and the 42W bonus pool are one short of the "Changsha bank Cup" Tencent yunqi innovation competition!
Bottomsheetdialogfragment + viewpager + fragment + recyclerview sliding problem
联想拯救者Y9000X 2020
[Journal Conference Series] IEEE series template download guide
Unified task distribution scheduling execution framework
2020年最新字节跳动Android开发者常见面试题及详细解析
X509 parsing
Scons build embedded ARM compiler
[point cloud series] so net: self organizing network for point cloud analysis
PyTorch 21. NN in pytorch Embedding module
Riscv MMU overview
Django::Did you install mysqlclient?
GDB的使用
Resolution: argument 'radius' is required to be an integer