Oracle数据库：oracle用命令定义非空not null，unique唯一性，主键primary key，外键foreign key，check检查，启用enable，禁用disable约束
2023-09-11 14:15:38 时间
Oracle数据库：oracle用命令定义非空not null，unique唯一性，主键primary key，外键foreign key，check检查，启用enable，禁用disable约束
2022找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开
测开的话，你就得学数据库，sql，oracle，尤其sql要学，当然，像很多金融企业、安全机构啥的，他们必须要用oracle数据库
这oracle比sql安全，强大多了，所以你需要学习，最重要的，你要是考网络警察公务员，这玩意你不会就别去报名了，耽误时间！
oracle系列文章：
【1】Oracle数据库：啥是oracle数据库？你为啥要学oracle？
【2】Oracle数据库：oracle 11g安装教程，已安装好的oracle各个文件夹的作用，oracle用户权限怎么样
【3】Oracle数据库：oracle启动，oracle客户端工具plsql安装教程和使用方法
【4】Oracle数据库：创建表空间，创建新用户，给用户分配对象、角色和系统权限，登录新用户建表
【5】Oracle数据库：链接配置，包括sqlnet.ora里面的transnames.ora配置数据库标识符SID，listener暂时简单了解
【6】Oracle数据库：net configureation assistant工具配置监听listener，配置本地网络访问服务器上的数据库
【7】Oracle数据库：oracle字符类型、数字类型、创建表表名的命名规则
【8】Oracle数据库：约束条件：主键约束、唯一约束、检查约束、非空约束、外键约束、默认值填写
【9】Oracle数据库：表的关系：一对多，一对一，多对多，一一继承和修改的关系，表不是重点，重点是数据和约束关系
【10】Oracle数据库：sql语言结构，数据查询语言DQL，select * from table；算术，别名，连接，去重等操作
【11】Oracle数据库：约束行限制where语句，判断条件，比较条件，字符串日期格式，in,like,escape,null语句
【12】Oracle数据库：逻辑运算and，or，not和各种运算的优先级控制
【13】Oracle数据库：排序order by语句，select from where order by的执行先后顺序，各种样例
【14】Oracle数据库：oracle函数，单行函数，多行函数，upper，lower，initcap，字符串函数
【15】Oracle数据库：数字函数，日期函数，round，trunc，mod，months_between,add_months,next_day,last_day,sysdate
【16】Oracle数据库：oracle数据类型转换to_char()日期和数字转字符,to_number()字符转数字,to_date()字符转日期函数
【17】Oracle数据库：oracle函数嵌套，nvl函数，nvl2函数，nullif函数，coalesce合并函数
【18】Oracle数据库：条件表达式case when then else end，decode函数，oracle单行函数练习示例
【19】Oracle数据库：oracle多表查询，等值连接，非等值连接，自连接的sql语句和规则
【20】Oracle数据库：oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)
【21】Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接
【22】Oracle数据库：oracle内连接inner join on，多表查询各种自链接、内连接、外连接的练习示例
【23】Oracle数据库：oracle组函数，聚合函数，多行函数，avg，sum，min，max，count，group by，having
【24】Oracle数据库：oracle嵌套分组函数（聚合函数），组函数的练习题，挺复杂的，用好decode函数，很有趣
【25】Oracle数据库：子查询、单行子查询，多行子查询，in，any，all语句，子查询的练习案例
————前面这些都是数据库查询语言的重要知识，一定要牢牢掌握和熟悉
后面的相对简单一点
【26】Oracle数据库：数据库操纵语言DML，插入insert into where，更新update where，删除delete where
【27】Oracle数据库：oracle事务处理语言TCL，commit，rollback，savepoint语句
【28】Oracle数据库：oracle数据定义语言DDL，查询表、序列、索引、视图、创建表，修改表的列alter，rename to，删除表数据truncate和表drop
oracle用命令定义约束
我们之前怎么玩呢？
都是根据可视化的工具
plsql搞
但是oracle实际上还是在底层会给你转化为命令
定义not null约束
sys_cn
n自动指针
约束可以加多个
关键字对了就行
constraint限制名字
不是constant哦
哈哈哈哈哈
SQL> create table dept80(id number,name varchar(20) not null,salary number constraint dept80_notnull not null);
Table created
SQL> desc dept80;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(20)
SALARY NUMBER
你可以看到nullable那个地方，有俩不行
一般情况下，我们是不会给它起名字的，没必要，系统自己搞就行了
SQL> alter table dept80 add(location_id number);
Table altered
SQL> desc dept80;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(20)
SALARY NUMBER
LOCATION_ID NUMBER Y
SQL> alter table dept80 modify(location_id not null);
Table altered
SQL> desc dept80;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(20)
SALARY NUMBER
LOCATION_ID NUMBER
通过修改语句搞定
懂了吧
定义unique约束
在括号的最后放约束，而不是前面的列直接约束？？？？
可能这样写比较简洁一些吧
但是直接在类型后面加约束也可以的
SQL> create table dept90(id number constraint dept90_uk unique,name varchar2(20));
Table created
SQL> desc dept90;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(20) Y
再看表长啥样
同理，修改表有可以搞定
SQL> alter table dept90 modify(name varchar2(20) not null);
Table altered
SQL> desc dept90;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(20)
很容易
SQL> alter table dept90 modify(name varchar2(20) unique);
Table altered
SQL> desc dept90;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(20)
这也是唯一的
定义primary key主键约束
跟前面都一样
SQL> create table dept70(id number constraint dept70_pk primary key, name varchar2(20));
Table created
SQL> desc dept70;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER
NAME VARCHAR2(20) Y
SQL> create table dept60(id number, name varchar2(20));
Table created
SQL> desc dept60;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(20) Y
SQL> alter table dept60 modify(id number primary key);
Table altered
SQL> desc dept60;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER
NAME VARCHAR2(20) Y
2个以上的primary key
SQL> create table dept50(id number primary key, name varchar2(20) primary key);
create table dept50(id number primary key, name varchar2(20) primary key)
ORA-02260: 表只能具有一个主键
这咋搞呢？
怎么联合呢？
前面不写
在创建表时，括号后面直接给constraint 起名 primary key(列1，列2)
这样放俩才行
SQL> create table dept50(id number, name varchar2(20), constraint dept_50_pk primary key(id, name));
Table created
SQL> desc dept50;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER
NAME VARCHAR2(20)
你看看
定义foreign key约束
只能在create的括号最后来添加，关键字多了
foreign key(表1的列) references 表2(表2的列)
俩表
SQL> create table dept40(id number, d_id number, constraint dept40_fk foreign key(d_id) references dept50(id));
create table dept40(id number, d_id number, constraint dept40_fk foreign key(d_id) references dept50(id))
ORA-02270: 此列列表的唯一关键字或主键不匹配
注意50表是有联合主键的
所以我们用60就行
SQL> create table dept40(id number, d_id number, constraint dept40_fk foreign key(d_id) references dept60(id));
Table created
SQL> desc dept40;
Name Type Nullable Default Comments
---- ------ -------- ------- --------
ID NUMBER Y
D_ID NUMBER Y
SQL>
我们用50表
添加一个列
SQL> alter table dept50 add(d_id number);
Table altered
SQL> desc dept50;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER
NAME VARCHAR2(20)
D_ID NUMBER Y
让它参照60的id
SQL> alter table dept50 modify(d_id number, constraint dept50_fk foreign key(d_id) references dept60(id));
Table altered
SQL> desc dept50;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER
NAME VARCHAR2(20)
D_ID NUMBER Y
好像通过直接add约束也行哦
这个牛逼
SQL> alter table dept50 add constraint dept50_fk foreign key(d_id) references dept60(id);
alter table dept50 add constraint dept50_fk foreign key(d_id) references dept60(id)
ORA-02275: 此表中已经存在这样的引用约束条件
这样也行哦
很强
定义检查约束check(a>b)
条件，就是要括号，优先级
SQL> desc dept30;
Name Type Nullable Default Comments
------ ----------- -------- ------- --------
ID NUMBER Y
SALARY NUMBER(8,2) Y
你插入数据试试
SQL> insert into dept30 values(1,1000);
insert into dept30 values(1,1000)
ORA-02290: 违反检查约束条件 (HR.DEPT30_CK)
SQL> insert into dept30 values(1,1001);
1 row inserted
SQL> desc dept30;
Name Type Nullable Default Comments
------ ----------- -------- ------- --------
ID NUMBER Y
SALARY NUMBER(8,2) Y
SQL> select * from dept30;
ID SALARY
---------- ----------
1 1001.00
SQL> alter table dept50 add constraint dept50_ck2 check(salary > 1000);
Table altered
搞定
禁用与启用约束条件disable
查这些约束在哪里？
SQL> select * from user_constraints;
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED
-------------------------------------------------------------------------------- ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- --------------
HR REGION_ID_NN C REGIONS "REGION_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR COUNTRY_ID_NN C COUNTRIES "COUNTRY_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR LOC_CITY_NN C LOCATIONS "CITY" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR DEPT_NAME_NN C DEPARTMENTS "DEPARTMENT_NAME" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR JOB_TITLE_NN C JOBS "JOB_TITLE" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR EMP_LAST_NAME_NN C EMPLOYEES "LAST_NAME" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR EMP_EMAIL_NN C EMPLOYEES "EMAIL" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR EMP_HIRE_DATE_NN C EMPLOYEES "HIRE_DATE" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR EMP_JOB_NN C EMPLOYEES "JOB_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR EMP_SALARY_MIN C EMPLOYEES salary > 0 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR JHIST_EMPLOYEE_NN C JOB_HISTORY "EMPLOYEE_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR JHIST_START_DATE_NN C JOB_HISTORY "START_DATE" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR JHIST_END_DATE_NN C JOB_HISTORY "END_DATE" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR JHIST_JOB_NN C JOB_HISTORY "JOB_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR JHIST_DATE_INTERVAL C JOB_HISTORY end_date > start_date ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR SYS_C0010892 O EMP_DETAILS_VIEW ENABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED GENERATED NAME 2022/10/21
HR SYS_C0011040 C EMP "ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2022/10/29
HR SYS_C0011051 C DEKT "LAST_NAME" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2022/10/29
HR SYS_C0011052 C DEKT "HIRE_DATE" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2022/10/29
HR SYS_C0011059 C DEPT80 "NAME" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2022/10/29
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED
-------------------------------------------------------------------------------- ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- --------------
HR DEPT80_NOTNULL C DEPT80 "SALARY" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/29
HR SYS_C0011061 C DEPT80 "LOCATION_ID" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2022/10/29
HR SYS_C0011063 C DEPT90 "NAME" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2022/10/29
HR DEPT30_CK C DEPT30 salary>1000 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/29
HR DEPT50_CK2 C DEPT50 salary > 1000 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/29
HR DEPT40_FK R DEPT40 HR SYS_C0011066 NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/29
HR DEPT50_FK R DEPT50 HR SYS_C0011066 NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/29
HR DEPT_MGR_FK R DEPARTMENTS HR EMP_EMP_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR EMP_MANAGER_FK R EMPLOYEES HR EMP_EMP_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR JHIST_EMP_FK R JOB_HISTORY HR EMP_EMP_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR EMP_JOB_FK R EMPLOYEES HR JOB_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR JHIST_JOB_FK R JOB_HISTORY HR JOB_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR EMP_DEPT_FK R EMPLOYEES HR DEPT_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR JHIST_DEPT_FK R JOB_HISTORY HR DEPT_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR DEPT_LOC_FK R DEPARTMENTS HR LOC_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR LOC_C_ID_FK R LOCATIONS HR COUNTRY_C_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR COUNTR_REG_FK R COUNTRIES HR REG_ID_PK NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21
HR COUNTRY_C_ID_PK P COUNTRIES ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21 HR COUNTRY_C_ID_PK
HR DEPT70_PK P DEPT70 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/29 HR DEPT70_PK
HR DEPT90_UK U DEPT90 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/29 HR DEPT90_UK
HR DEPT_50_PK P DEPT50 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/29 HR DEPT_50_PK
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED
-------------------------------------------------------------------------------- ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- --------------
HR DEPT_ID_PK P DEPARTMENTS ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21 HR DEPT_ID_PK
HR EMP_EMAIL_UK U EMPLOYEES ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21 HR EMP_EMAIL_UK
HR EMP_EMP_ID_PK P EMPLOYEES ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21 HR EMP_EMP_ID_PK
HR JHIST_EMP_ID_ST_DATE_PK P JOB_HISTORY ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21 HR JHIST_EMP_ID_ST_DATE_PK
HR JOB_ID_PK P JOBS ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21 HR JOB_ID_PK
HR LOC_ID_PK P LOCATIONS ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21 HR LOC_ID_PK
HR REG_ID_PK P REGIONS ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/21 HR REG_ID_PK
HR SYS_C0011064 U DEPT90 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2022/10/29 HR SYS_C0011064
HR SYS_C0011066 P DEPT60 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2022/10/29 HR SYS_C0011066
50 rows selected
所有表的约束信息
老多了
SQL> desc user_constraints;
Name Type Nullable Default Comments
----------------- ------------ -------- ------- ---------------------------------------------------------------------------
OWNER VARCHAR2(30) Y Owner of the table
CONSTRAINT_NAME VARCHAR2(30) Name associated with constraint definition
CONSTRAINT_TYPE VARCHAR2(1) Y Type of constraint definition
TABLE_NAME VARCHAR2(30) Name associated with table with constraint definition
SEARCH_CONDITION LONG Y Text of search condition for table check
R_OWNER VARCHAR2(30) Y Owner of table used in referential constraint
R_CONSTRAINT_NAME VARCHAR2(30) Y Name of unique constraint definition for referenced table
DELETE_RULE VARCHAR2(9) Y The delete rule for a referential constraint
STATUS VARCHAR2(8) Y Enforcement status of constraint - ENABLED or DISABLED
DEFERRABLE VARCHAR2(14) Y Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE
DEFERRED VARCHAR2(9) Y Is the constraint deferred by default - DEFERRED or IMMEDIATE
VALIDATED VARCHAR2(13) Y Was this constraint system validated? - VALIDATED or NOT VALIDATED
GENERATED VARCHAR2(14) Y Was the constraint name system generated? - GENERATED NAME or USER NAME
BAD VARCHAR2(3) Y Creating this constraint should give ORA-02436. Rewrite it before 2000 AD.
RELY VARCHAR2(4) Y If set, this flag will be used in optimizer
LAST_CHANGE DATE Y The date when this column was last enabled or disabled
INDEX_OWNER VARCHAR2(30) Y The owner of the index used by the constraint
INDEX_NAME VARCHAR2(30) Y The index used by the constraint
INVALID VARCHAR2(7) Y
VIEW_RELATED VARCHAR2(14) Y
单独看某个表？
SQL> select * from user_constraints c where c.TABLE_NAME='DEPT40';
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED
-------------------------------------------------------------------------------- ------------------------------ --------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- ----------- ------------------------------ ------------------------------ ------- --------------
HR DEPT40_FK R DEPT40 HR SYS_C0011066 NO ACTION ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2022/10/29
SQL>
再筛选
SQL> select c.CONSTRAINT_NAME,c.CONSTRAINT_TYPE,c.SEARCH_CONDITION from user_constraints c where c.TABLE_NAME='DEPT40';
CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
------------------------------ --------------- --------------------------------------------------------------------------------
DEPT40_FK R
SQL>
约束的名字
约束的类型
搜索条件
type是啥呢？R
貌似是foreigg的缩写
c是check
SQL> alter table dept40 add(salary number(8,2), check(salary > 1000));
Table altered
SQL> select c.CONSTRAINT_NAME,c.CONSTRAINT_TYPE,c.SEARCH_CONDITION from user_constraints c where c.TABLE_NAME='DEPT40';
CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
------------------------------ --------------- --------------------------------------------------------------------------------
DEPT40_FK R
SYS_C0011072 C salary > 1000
加一个约束之后就OK了
关键字：disable cascade，级联这个表的其他约束
这和那个bias系统一样
SQL> alter table dept40 disable constraint sys_c0011072;
Table altered
直接禁止
然后加数据看看
如果是带外键的dept50的d_id，参照dept60
这个时候，你要禁用的话，需要cascade
SQL> desc dept60;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER
NAME VARCHAR2(20) Y
SQL> desc dept50;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
ID NUMBER
NAME VARCHAR2(20)
D_ID NUMBER Y
SALARY NUMBER Y
这时候要禁用dept60的pk
因为它挂着表50的
SQL> alter table dept60 disable constraint SYS_C0011066 ;
alter table dept60 disable constraint SYS_C0011066
ORA-02297: 无法禁用约束条件 (HR.SYS_C0011066) - 存在相关性
存在相关性，为啥呢？因为60表外挂这50表，所以我们需要级联cascade禁用
SQL> alter table dept60 disable constraint SYS_C0011066 cascade;
Table altered
这样就可以级联操作了，别的外挂表的约束条件也禁用了
刺激
好说
启用约束条件enable
你要是启用，就是enable
又把上面的例子启用
SQL> alter table dept60 enable constraint SYS_C0011066 ;
Table altered
就一字之差
相当于可视化的工具点一下也行
OK
在这里插入代码片
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
相关文章
- oracle学习
- oracle导入命令，记录一下 数据库日志太大，清理日志文件
- Oracle数据库经常使用经典查询
- Oracle数据库刷题笔记，mysql刷题笔记
- Oracle数据库：oracle嵌套分组函数（聚合函数），组函数的练习题，挺复杂的，用好decode函数，很有趣
- Oracle数据库：oracle内连接inner join on，多表查询各种自链接、内连接、外连接的练习示例
- 搜狐畅游高级DBA：Oracle运维中的实战经验和应对技巧
- Windos系统用PL/SQL Developer软件进行Oracle数据库备份/迁移
- JDBC连接Oracle数据库的示例代码
- oracle函数 decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
- 转 Java连接Oracle数据库的简单示例
- 【转】Oracle数据库中Sequence的用法
- Oracle 10g RAC TAF
- [Oracle工程师手记]包含结构体的向数据库表插入数据的PL/SQL匿名块例子
- [Oracle]数据库的Control File 取Dump后的样例
- Oracle 单实例数据库安装和real application clusters数据库安装的区别
- Oracle数据库题库
- Oracle数据库sql常用