oracle constraint_type 问题

本文详细解释了Oracle数据库中各种约束类型的定义与作用,包括主键、外键、唯一键、检查约束和非空约束,并提供了相关代码示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

select distinct constraint_type from dba_constraints;

结果:
CONSTRAINT_TYPE

?
C --check
O
P   --primary key
R   --forgien key
U   --unique
V

其他三个是什么?
constraint的类型:
1、primary key      unique and not null,可以是多个column的联合。composite pk只能被定义为table constraint
2、foreign key      parent table 中的primary key中的values必须包含child table中所有的values. share column的parent-child 关系
3、unique
4、check    present in a static list of values permitted for the column.
5、not null 只能被定义在column constraint
(2,3,4允许null)

Type Code   Type Description                                    Acts On Level
C                 Check on a table                                     Column
O                             Read Only                                 on a view Object
P                             Primary Key                               Object
R                         Referential AKA Foreign Key             Column
U                           Unique Key                          Column
V                         Check Option                         on a view Object


本文来自优快云博客,转载请标明出处:http://blog.youkuaiyun.com/yanleigis/archive/2008/09/23/2965740.aspx

1、达梦数据库使用以下SQL查询元数据信息发现无法查出主键、外键、索引信息 2、SELECT A.TABLE_NAME TABLE_NAME , A.COLUMN_NAME NAME , A.DATA_TYPE TYPENAME , A.DATA_LENGTH LENGTH , A.DATA_PRECISION PRECISION , A.DATA_SCALE SCALE , A.DATA_DEFAULT , A.NULLABLE , DECODE(B.COMMENTS, NULL, A.COLUMN_NAME, B.COMMENTS) DESCRIPTION, ( SELECT COUNT(*) FROM USER_CONSTRAINTS CONS, USER_CONS_COLUMNS CONS_C WHERE CONS.CONSTRAINT_NAME=CONS_C.CONSTRAINT_NAME AND CONS.CONSTRAINT_TYPE='P' AND CONS.TABLE_NAME =B.TABLE_NAME AND CONS_C.COLUMN_NAME =A.COLUMN_NAME AND CONS.OWNER ='IBPS_SAAS_SAAS_TEST_ZTESTZ_IBPS_BUSINESS_PROVIDER' ) AS IS_PK, ( SELECT COUNT(*) FROM USER_CONSTRAINTS CONS, USER_CONS_COLUMNS CONS_C WHERE CONS.CONSTRAINT_NAME=CONS_C.CONSTRAINT_NAME AND CONS.CONSTRAINT_TYPE='R' AND CONS.TABLE_NAME =B.TABLE_NAME AND CONS_C.COLUMN_NAME =A.COLUMN_NAME AND CONS.OWNER ='IBPS_SAAS_SAAS_TEST_ZTESTZ_IBPS_BUSINESS_PROVIDER' ) AS IS_FK, ( SELECT COUNT(*) FROM USER_IND_COLUMNS, USER_INDEXES WHERE USER_IND_COLUMNS.INDEX_NAME = USER_INDEXES.INDEX_NAME AND USER_IND_COLUMNS.TABLE_NAME =B.TABLE_NAME AND USER_IND_COLUMNS.COLUMN_NAME=A.COLUMN_NAME AND USER_INDEXES.TABLE_OWNER ='IBPS_SAAS_SAAS_TEST_ZTESTZ_IBPS_BUSINESS_PROVIDER' ) AS IS_IDX FROM ALL_TAB_COLUMNS A, ALL_COL_COMMENTS B WHERE A.COLUMN_NAME =B.COLUMN_NAME AND A.OWNER = B.SCHEMA_NAME AND A.TABLE_NAME = B.TABLE_NAME AND upper(A.TABLE_NAME)='ZTESTZ_DEC' AND A.OWNER ='IBPS_SAAS_SAAS_TEST_ZTESTZ_IBPS_BUSINESS_PROVIDER' ORDER BY A.COLUMN_ID
03-08
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值