DDL 约束条件

http://www.verejava.com/?id=1717412284582

1 . 约束条件
  Oracle数据库的约束包括:
    NOT NULL  非空约束  
    UNIQUE    唯一键约束 
    PRIMARY KEY 主键约束  ----重点
    FOREIGN KEY 外键约束  ----重点
      
  
  1. NOT NULL   如果一个字段有非空约束插入数据时该字段必须给定一个值
     CREATE TABLE student
        (
      id int(10) NOT NULL,
      name VARCHAR(30) NOT NULL
        );
      
  
    //插入值判断是否合法
    INSERT INTO student(id,name) VALUES(1,'张见');
    INSERT INTO student(id,name) VALUES(2,NULL);
    UPDATE student SET id=NULL WHERE id=1;
    
  
  2. PRIMARY KEY 主键约束(参照完整性约束)
    主键是唯一标识表中的某一行记录, 相当于非空且唯一
    1 . 单主键
    DROP TABLE student;
      
    //表级约束
    CREATE TABLE student
        (
      id int(10) ,
      name VARCHAR(30) NOT NULL,
      PRIMARY KEY  (id)
        );
  
    //插入值判断是否合法
    INSERT INTO student(id,name) VALUES(1,'张见');
    INSERT INTO student(id,name) VALUES(2,'张见');
    INSERT INTO student(id,name) VALUES(1,'张见');
    INSERT INTO student(id,name) VALUES(NULL,'张见');
  
    1 . 联合主键
    联合唯一键的组合(id+subject)不能重复
    CREATE TABLE scores
      (
      id int(10),
        subject VARCHAR(30),
        score int(3),
        PRIMARY KEY  (id,subject)
        );
    //插入值
    INSERT INTO scores(id,subject,score) VALUES(1,'语文',90);
    INSERT INTO scores(id,subject,score) VALUES(1,'数学',90);
    INSERT INTO scores(id,subject,score) VALUES(2,'语文',90);
    //不合法
    INSERT INTO scores(id,subject,score) VALUES(2,'语文',90);
  
  3. FOREIGN KEY 外键约束
    外键约束 确保两个字段之间的参照完整性.
    外键约束的特性:
      1. 子表外键字段的值必须在主表参照字段的范围内, 或者为空值 NULL
      2. 外键参照的必须是主表的主键或者是唯一键
      3. 主表主键或唯一键被子表参照时, 主表对应的记录不允许删除.
      4. 创建数据表时, 先创建主表, 然后再创建参照他的子表, 删除表时正好相反
      5. 添加数据时. 先向主表添加记录, 然后再向子表添加记录, 删除正好相反
      6. 实际开发中也在也有可能应用层控制参照完整性, 有利于数据移植
    例如:
    1. 1:1 的关联表 创建一个员工信息表:
      CREATE TABLE emp
        (
        id int(10) ,
        name VARCHAR(30),
        job VARCHAR(30),
        PRIMARY KEY (id)
        );
      创建一员工工资表:
      CREATE TABLE salary
        (
        id int(10) PRIMARY KEY,
        emp_id int(10),
        salary float,
        FOREIGN KEY (emp_id) REFERENCES emp(id) 
        );
  
      向员工表 emp 插入数据
       INSERT INTO emp(id,name,job)VALUES(1,'张浩','程序员');
       INSERT INTO emp(id,name,job)VALUES(2,'李洁','测试');
  
      向员工工资表插入数据
      //合法
      INSERT INTO salary(id,emp_id,salary)VALUES(1,1,5000);
      INSERT INTO salary(id,emp_id,salary)VALUES(2,NULL,5000);
      DELETE FROM emp WHERE id=2;
  
      //非法
      INSERT INTO salary(id,emp_id,salary)VALUES(3,3,5000);
      DELETE FROM emp WHERE id=1;
      DROP TABLE emp;
  
    2. 两个表的关联查询
      查出所有员工的和对应的工资信息
      SELECT a.name  ,b.salary  FROM emp a,salary b WHERE a.id=b.emp_id ;
  
    3. 1:多  查询所有员工,显示出部门名称
      创建一个部门表
      CREATE TABLE dept
      (
          id int(10) ,
          name VARCHAR(10),
          PRIMARY KEY (id)
      );
      向部门表插入数据
      INSERT INTO dept(id,name)VALUES(1,'IT');
      INSERT INTO dept(id,name)VALUES(2,'财务');
  
      创建一个员工表
      CREATE TABLE emp
        (
        id int(10),
        name VARCHAR(10),
        salary int(10),
        dept_id int(10),
        FOREIGN KEY (dept_id) REFERENCES dept(id) 
        );
      向员工表插入数据
      INSERT INTO emp(id,name,salary,dept_id)VALUES(1,'王浩',6000,1);
      INSERT INTO emp(id,name,salary,dept_id)VALUES(2,'李洁',5000,1);
      INSERT INTO emp(id,name,salary,dept_id)VALUES(3,'张强',6000,2);
      INSERT INTO emp(id,name,salary,dept_id)VALUES(4,'张涛',8000,2);
  
      查询所有员工,显示出部门名称
      SELECT a.name  ,b.*  FROM dept a,emp b WHERE a.id=b.dept_id ;
  
    4. 多:多 表连接
      错误的创建多表关联
      创建一个老师表
      CREATE TABLE teacher
      (
        id int(10) PRIMARY KEY,
        name VARCHAR(20),
        student_id int(10),
        FOREIGN KEY (student_id) REFERENCES student(id) 
      );
  
      创建一个学生表
      CREATE TABLE student
      (
        id int(10) ,
        name VARCHAR(20),
        teacher_id int(10),
        PRIMARY KEY (id),
        FOREIGN KEY (teacher_id) REFERENCES teacher(id) 
      );
  
      /
      解决办法:  关联表技术
      创建一个老师表
      CREATE TABLE teacher
      (
        id int(10),
        name VARCHAR(20),
        PRIMARY KEY (id)
      );
      插入数据
      INSERT INTO teacher(id,name)VALUES(1,'李老师');
      INSERT INTO teacher(id,name)VALUES(2,'张老师');
      INSERT INTO teacher(id,name)VALUES(3,'马老师');
  
      创建一个学生表
      CREATE TABLE student
      (
        id int(10) PRIMARY KEY,
        name VARCHAR(20),
        PRIMARY KEY (id)
      );
      INSERT INTO student(id,name)VALUES(1,'李洁');
      INSERT INTO student(id,name)VALUES(2,'张涛');
      INSERT INTO student(id,name)VALUES(3,'王军');
      INSERT INTO student(id,name)VALUES(4,'李密');
  
      创建 老师和学生的关联表
      CREATE TABLE teacher_student
      (
        teacher_id int(10),
        student_id int(10),
        FOREIGN KEY (teacher_id) REFERENCES teacher(id), 
        FOREIGN KEY (student_id) REFERENCES student(id) 
      );
      插入关联数据
      INSERT INTO teacher_student(teacher_id,student_id)VALUES(1,1);
      INSERT INTO teacher_student(teacher_id,student_id)VALUES(1,2);
      INSERT INTO teacher_student(teacher_id,student_id)VALUES(2,2);
      INSERT INTO teacher_student(teacher_id,student_id)VALUES(2,3);
      INSERT INTO teacher_student(teacher_id,student_id)VALUES(2,4);
  
      查找出  李老师 教的所有学生信息
      SELECT a.name teacher,c.name student FROM teacher a, teacher_student b,student c WHERE a.id=b.teacher_id AND b.student_id=c.id AND a.id=1;
        
      查找出教过 张涛 的所有老师信息
      SELECT a.name teacher,c.name student FROM teacher a, teacher_student b,student c WHERE a.id=b.teacher_id AND b.student_id=c.id AND c.id=2;

http://www.verejava.com/?id=1717412284582