欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

数据库六大约束用法:主键(primary key)、外键(foreign key)、非空(not null)、默认(default)、检查(check)、唯一(unique)

程序员文章站 2022-06-01 17:47:57
...

1. 数据库有六大约束

  1. 主键(primary key)
  2. 外键(foreign key):被参照的键必须有唯一约束或是主键
  3. 非空(not null)
  4. 默认(default)
  5. 检查(check):oracle独有
  6. 唯一(unique)

2. 六大约束的用法

以下所有演示的SQL语句都是基于Oracle,可能在MySQL中使用有些出入。不过不用担心,后面会指出一些MySQL与Oracle的不同之处

-- 1.创建部门表dept   用于演示外键
create table test_dept(
    deptno varchar2(20) primary key,
    dname varchar2(20),
    loc varchar2(20)unique
);

-- 2.创建员工表emp  
create table test_emp(
    -- 1.员工id:添加主键约束primary key
    eid varchar(20) primary key,
    -- 2.员工名字:添加非空约束not null,唯一约束unique
    ename varchar(20) not null unique, 
    -- 3.员工性别与年龄:添加检查约束check
    sex char(2) check(sex in ('男','女')),
    age number(3) check(age>0),
    -- 4.员工工资:添加默认约束default
    sal number(7,2) default('3000'),
    -- 5.员工所属部门:添加外键约束forein key
    deptno references test_dept(deptno),
    -- 6.部门地址:用于单独添加外键约束演示
    loc varchar2(20)  
);

--  单独添加或修改check约束
alter table test_emp add constraint age check (age>0 and age<140);
--- 单独添加外键约束
alter table test_emp add constraint fk_loc foreign key(loc) references test_dept(loc);



-- 也可以将check约束和主键约束放在最后
create table test_emp2(
    -- 1.员工id:主键约束primary key
    eid varchar(20),
    -- 2.员工名字:非空约束not null,唯一约束unique
    ename varchar(20) not null unique, 
    -- 3.员工性别与年龄:检查约束check
    sex char(2),
    age number(3),
    -- 4.员工工资:默认约束default
    sal number(7,2) default('3000'),
    -- 5.员工所属部门:外键约束forein key
    deptno references test_dept(deptno),
    -- 6.部门地址:用于单独添加外键约束演示
    loc varchar(20),
    primary key(eid),check(sex in ('男','女')),check(age>0)
);


-- default约束用法:
-- 1.默认为空:default null
-- 2.默认值类型为字符串:default '普通用户' 或default('普通用户')
-- 3.默认值类型为数值:default 2000 或default(2000)
-- 推荐使用不需要括号方式:default 'admin' 或 default 2000
-- oracle中:default约束必须放在非空约束(not null)前面
create table test_emp3(
    -- 1.员工id:主键约束primary key
    eid varchar(20),
    -- 2.员工名字:非空约束not null,唯一约束unique
    ename varchar(20), 
    -- 3.员工性别与年龄:检查约束check
    sex char(2),
    age number(3),
    -- 4.员工工资:默认约束default
    sal number(7,2) default(533) not null ,
    -- 5.员工所属部门:外键约束foreign key
    deptno references test_dept(deptno),
    -- 6.部门地址:用于单独添加外键约束演示
    loc varchar2(20),
    check(sex in ('男','女')),check(age>0)
);
-- 单独添加主键
alter table test_emp3 add constraint pk_ename primary key(ename);

-- 一次添加多个主键
-- 注意:1.一次添加多个主键,只能采取下列方式(使用GUI工具除外);
--      2.不能使用分别在eid和ename后面加primary key
--      3.也不能先给eid设置为primary key ,在表创建完毕后在使用alter去给ename添加主键
create table test_emp4(
    -- 1.员工id:主键约束primary key
    eid varchar(20),
    -- 2.员工名字:非空约束not null,唯一约束unique
    ename varchar(20), 
    -- 3.员工性别与年龄:检查约束check
    sex char(2),
    age number(3),
    -- 4.员工工资:默认约束default
    sal number(7,2) default(533) not null ,
    -- 5.员工所属部门:外键约束forein key
    deptno references test_dept(deptno),
    -- 6.部门地址:用于单独添加外键约束演示
    loc varchar2(20),
    primary key(eid,ename),check(sex in ('男','女')),check(age>0)
);


3. mysql中如何实现类似于oracle的检查约束

可以采用枚举enum来实现类似检查约束的功能。

CREATE TABLE `testuser` (
  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(12) NOT NULL DEFAULT '',
  `real_name` VARCHAR(12) NOT NULL DEFAULT '',
  `password` VARCHAR(8) NOT NULL DEFAULT '',
  `sex` ENUM('0','1') NOT NULL DEFAULT '0',
  `degree` ENUM('1','2','3','4') NOT NULL DEFAULT '1',
  `birthday` DATETIME DEFAULT NULL,
  PRIMARY KEY  (`user_id`)
);

4.mysql创建外键

(1)创建表时添加外键

添加一个外键

create table users( 
    username varchar(50), 
    role_id int , 
    constraint fk_users_roles foreign key (role_id) references roles(roleid) 
);

可以添加两个主键

CREATE TABLE roles_menus ( 
    roles_id INT , 
    menus_id INT, 
    PRIMARY KEY (roles_id, menus_id), 
    CONSTRAINT fk_users_roles FOREIGN KEY (roles_id) REFERENCES roles(roleid), 
    CONSTRAINT fk_users_menus FOREIGN KEY (menus_id) REFERENCES menus(menuid) 
);

(2)创建表后,单独添加外键约束

create table users( 
    username varchar (50) primary key, 
    role_id int 
);

alter table users add constraint fk_users_roles foreign key (role_id) references roles(roleid);

相关标签: 约束