当前位置:网站首页>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
边栏推荐
- Software test system integration project management engineer full truth simulation question (including answer and analysis)
- Solve the problem that Oracle needs to set IP every time in the virtual machine
- 零拷贝技术
- X509 parsing
- Scons build embedded ARM compiler
- 缘结西安 | CSDN与西安思源学院签约,全面开启IT人才培养新篇章
- 【官宣】长沙软件人才实训基地成立!
- 顶级元宇宙游戏Plato Farm,近期动作不断利好频频
- Set Jianyun x Feishu Shennuo to help the enterprise operation Department realize office automation
- Detailed explanation of ADB shell top command
猜你喜欢
SPI NAND flash summary
面试官给我挖坑:单台服务器并发TCP连接数到底可以有多少 ?
【官宣】长沙软件人才实训基地成立!
[point cloud series] neural opportunity point cloud (NOPC)
[indicators] precision, recall
9419 page analysis of the latest first-line Internet Android interview questions
Usereducer basic usage
QT调用外部程序
[barycentric coordinate interpolation, perspective correction interpolation] principle and usage opinions
Imx6ull QEMU bare metal tutorial 1: GPIO, iomux, I2C
随机推荐
The interviewer dug a hole for me: how many concurrent TCP connections can a single server have?
MySQL 8.0.11 download, install and connect tutorials using visualization tools
Remove the status bar
爱可可AI前沿推介 (4.23)
Playwright contrôle l'ouverture de la navigation Google locale et télécharge des fichiers
[tensorflow] sharing mechanism
./gradlew: Permission denied
Android clear app cache
Machine learning -- model optimization
JS compares different elements in two arrays
Feature Engineering of interview summary
Analysis of the latest Android high frequency interview questions in 2020 (BAT TMD JD Xiaomi)
[point cloud series] multi view neural human rendering (NHR)
[point cloud series] Introduction to scene recognition
Comparison and summary of applicable scenarios of Clickhouse and MySQL database
Lenovo Savior y9000x 2020
“湘见”技术沙龙 | 程序员&CSDN的进阶之路
Usereducer basic usage
TIA博途中基于高速计数器触发中断OB40实现定点加工动作的具体方法示例
JS time to get this Monday and Sunday, judge the time is today, before and after today