表的约束--主键约束 primary key
(2017-07-08 22:33:12)分类: Oracle |
1.约束 constraint
数据完整性:
1.1 约束的概念
对表中的字段添加的限制
1.2 约束的具体类型
1)主健约束 primary key
主键是指能唯一标识一条记录的单个数据表列或联合的数据表列(联合主键或复合主键)。
2)唯一约束 unique
3)非空约束 not null
4)检查约束 check
5)外键约束 foreign key references
1.3 约束的实现方式
1)列级约束:创建表时,定义完一个字段后直接在后边添加约束
2)表级约束:定义完一个表的所有字段后,用逗号隔开,再添加约束
2 主健约束
2.1 列级实现
1)自己不给约束起名字,则系统自动提供一个约束名。
create table testcons_lzh_88(
insert into testcons_lzh_88 values(1, 'test1');
insert into testcons_lzh_88 values(2, 'test2');
SQL> select * from testcons_lzh_88;
SQL> insert into testcons_lzh_88 values(1, 'test3');
SQL> insert into testcons_lzh_88 values(null,'test3');
insert into testcons_lzh_88 values(null,'test3')
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPENLAB"."TESTCONS_LZH_88"."ID")
2)手动为约束命名
表名_字段_约束类型
字段 数据类型 constraint 约束名 约束类型
id
drop table testcons_lzh_88;
create table testcons_lzh_88(
SQL> insert into testcons_lzh_88 values(1,'test1');
1 row created.
SQL> /
insert into testcons_lzh_88 values(1,'test1')
*
ERROR at line 1:
ORA-00001: unique constraint (OPENLAB.TESTCONS_LZH_88_ID_PK) violated
2.2 表级实现
复合主健(组合键,联合主键)-- 只能通过表级实现
drop table testcons_lzh_88;
create table testcons_lzh_88(
userid number(10) primary key,
*
ERROR at line 3:
ORA-02260: table can have only one primary key
create table testcons_lzh_88(
insert into testcons_lzh_88 values(1, 1,
'test1');
insert into testcons_lzh_88 values(1, 2,
'test1');
insert into testcons_lzh_88 values(2, 1,
'test1');
insert into testcons_lzh_88 values(2, 2,
'test1');
insert into testcons_lzh_88 values(2, 2,
'test1');
注意:不建议使用复合主键
2.3 alter table方法
格式:ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (col1, col2,...coln);
示例:
先创建两张表(tb_employees和tb_departments)
create table tb_employees
(
employee_id number,
employee_name varchar2(50),
employee_age number,
employee_birth date,
department_id number
);
create table tb_departments
(
department_id number,
department_name varchar2(100),
location varchar2(300)
);
通过alter table语法创建主键:
--单列主键
alter table tb_employees add constraint tb_employees_pk primary key (employee_id);
--联合主键
alter table tb_departments add constraint tb_departments_pk primary key (department_id,department_name);
2.4 创建自增主键
oracle建立自增主键
1)建立一张表
CREATE TABLE example(
ID Number(4) NOT NULL PRIMARY KEY,
NAME VARCHAR(25),
PHONE VARCHAR(10),
ADDRESS VARCHAR(50));
2)自定义序列sequence
CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加,不循环
NOCACHE -- 不建缓冲区
建立一个序列(sequence),名称为emp_sequence,范围是从1开始到无限大(无限大的程度是由你机器决定的),nocycle 是决定不循环,如果你设置了最大值那么你可以用cycle 会使seq到最大之后循环.对于nocache顺便说一下如果你给出了cache值那么系统将自动读取你的cache值大小个seq,这样在反复操作时会加快运行速度,但如果遭遇意外情况如当机了或oracle死了,则下次取出的seq值将和上次的不连贯.如果连不连贯无所谓建议用cache
3)创建一个触发器来执行它!代码如下:
CREATE TRIGGER
"触发器名称"
BEFORE
INSERT ON example FOR EACH ROW WHEN (new.id is null)
begin
select emp_sequence.nextval into :new.id from dual;
end;
INSERT INTO example(Name,phone,address) Values('Cao','56498543','Heibei');
2.5 禁用主键
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
alter table tb_employees disable constraint tb_employees_pk;
2.6 启用主键
语法:
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
示例:
alter table tb_employees enable constraint tb_employees_pk;
2.7 删除主键
语法:
有命名:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
无命名:
可用 SELECT
查找表中主键名称得student表中的主键名为SYS_C002715
alter table student drop constraint SYS_C002715;
示例:
alter table tb_employees drop constraint tb_employees_pk;
alter table tb_departments drop constraint tb_departments_pk;
alter table TB_PK_EXAMPLE drop constraint TB_PK_EXAMPLE_PK;
alter table TB_PRODUCT_EX drop constraint TB_PRODUCT_EX_PK;
2.8 Oracle中查询表有没有建立主键?
一般的情况下,表的主键是必要的,没有主键的表可以说是不符合设计规范的。
SELECT table_name
FROM User_tables t
WHERE NOT EXISTS
(SELECT table_name
FROM User_constraints
c
WHERE constraint_type = 'P' AND t.table_name=c.table_name)
相关数据字典解释
user_tables
user_tab_columns
user_constraints
user_cons_columns
user_indexes