加载中…
个人资料
  • 博客等级:
  • 博客积分:
  • 博客访问:
  • 关注人气:
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
正文 字体大小:

表的约束--主键约束 primary key

(2017-07-08 22:33:12)
分类: Oracle

1.约束 constraint

数据完整性:

    实体完整性      同一表中不能存在完全相同的两行

    域完整性        某一列上的约束

    引用完整性      两个表中,表a引用表b中的列,a表中该列的值必须在b表中存在

    自定义完整性    

1.1 约束的概念

对表中的字段添加的限制    

1.2 约束的具体类型

1)主健约束 primary key 

    值唯一 并且 非空

    一个表中只能有个一个主健约束

主键是指能唯一标识一条记录的单个数据表列或联合的数据表列(联合主键或复合主键)。

 

2)唯一约束 unique

    值不能重复,但是允许为空,并且空值允许有多个

    一个表中可以有多个字段设置为唯一约束


3)非空约束 not null

    值不能为null


4)检查约束 check

    值必须能够使检查约束表达式为真


5)外键约束 foreign key references

    

1.3 约束的实现方式

1)列级约束:创建表时,定义完一个字段后直接在后边添加约束

2)表级约束:定义完一个表的所有字段后,用逗号隔开,再添加约束


2 主健约束

2.1 列级实现

1)自己不给约束起名字,则系统自动提供一个约束名。

create table testcons_lzh_88(

        id number(7) primary key,

        name varchar(20)

        );

insert into testcons_lzh_88 values(1, 'test1');

insert into testcons_lzh_88 values(2, 'test2');


SQL> select * from testcons_lzh_88;

    ID NAME

    ---------- ------------------------------

         1 test1

         2 test2

 

SQL> insert into testcons_lzh_88 values(1, 'test3');

     insert into testcons_lzh_88 values(1, 'test3')

                                       *

     ERROR at line 1:

     ORA-00001: unique constraint (OPENLAB.SYS_C0027439) violated


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)手动为约束命名

表名_字段_约束类型  testcons_lzh_88_id_pk

字段 数据类型 constraint 约束名 约束类型

id  number(7) constraint testcons_lzh_88_id_pk primary key;

drop table testcons_lzh_88;

create table testcons_lzh_88(

        id number(7) constraint testcons_lzh_88_id_pk primary key,

        name varchar2(30)

        );


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(

        id number(7) primary key,

        userid number(10) primary key,

        name varchar2(20)

        );

userid number(10) primary key,

*

ERROR at line 3:

ORA-02260: table can have only one primary key


create table testcons_lzh_88(

        id number(7),

        userid number(10),

        name varchar2(20),

        constraint testcons_lzh_88_userid_pk primary key(id, userid)

        );


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   *   from   user_cons_columns;

查找表中主键名称得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       索引


0

阅读 收藏 喜欢 打印举报/Report
  

新浪BLOG意见反馈留言板 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

新浪公司 版权所有