对比SQL SERVER，ORACLE，DB2上约束建立语句！

约束用于强制行数据满足特定的商业规则（数据类型是强制列的数据满足规则） 

约束有以下五种类型

•NOT NULL

•UNIQUE

•PRIMARY KEY

•FOREIGN KEY

•CHECK

 

SQL SERVER上的NOT NULL约束：

•CREATE TABLE U_emp(            

•   empno bigint,         

•  ename VARCHAR(10) NOT NULL, 

•  job VARCHAR(9),             

•  mgr bigint,               

•  hiredate DATE,              

•  sal decimal(7,2),             

•  comm decimal(7,2),         

•  deptno decimal(7,2) NOT NULL);

 

ORACLE上的NOT NULL约束：

 CREATE TABLE emp(

   empno NUMBER(4),

   ename VARCHAR2(10) NOT NULL,

   job VARCHAR2(9),

   mgr NUMBER(4),

   hiredate DATE,

   sal NUMBER(7,2),

   comm NUMBER(7,2),

   deptno NUMBER(7,2) NOT NULL);

 

DB2上的NOT NULL约束：

CREATE TABLE U_emp(            

   empno INTEGER,         

  ename VARCHAR(10) NOT NULL, 

  job VARCHAR(9),             

  mgr INTEGER,               

  hiredate DATE,              

  sal DECIMAL(7,2),             

  comm DECIMAL(7,2),         

  deptno DECIMAL(7,2) NOT NULL);

 

SQL SERVER上的UNIQUE约束：

CREATE TABLE   U_dept(                     

            deptno   INTEGER,                

          dname   VARCHAR(14),                   

          loc   VARCHAR(13),                     

          CONSTRAINT dept_dname_uk UNIQUE(dname));

 

ORACLE上的UNIQUE约束：

 CREATE TABLE   dept(

   deptno   NUMBER(2),

   dname   VARCHAR2(14),

   loc   VARCHAR2(13),

   CONSTRAINT dept_dname_uk UNIQUE(dname));

 

DB2上的UNIQUE约束：

 CREATE TABLE   U_dept(                     

            deptno   INTEGER,                

          dname   VARCHAR(14) not null,                   

          loc   VARCHAR(13),                     

          CONSTRAINT dept_dname_uk UNIQUE(dname));

 

SQL SERVER上的PK 约束：

CREATE TABLE   P_dept(                            

   deptno   INTEGER,                            

  dname   VARCHAR(14),                            

  loc   VARCHAR(13),                              

  CONSTRAINT dept_dname_uk1 UNIQUE (dname),       

  CONSTRAINT dept_deptno_pk1 PRIMARY KEY(deptno));

 

ORACLE上的PK约束

CREATE TABLE   dept(

    deptno   NUMBER(2),

   dname   VARCHAR2(14),

   loc   VARCHAR2(13),

   CONSTRAINT dept_dname_uk UNIQUE (dname),

   CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

 

DB2和的PK约束：

CREATE TABLE   P_dept(                            

   deptno   INTEGER not null,                            

  dname   VARCHAR(14) not null,                            

  loc   VARCHAR(13),                              

  CONSTRAINT dept_dname_uk1 UNIQUE (dname),       

  CONSTRAINT dept_deptno_pk1 PRIMARY KEY(deptno));

 

SQL SERVER上的FK 约束：

CREATE TABLE F_emp(                             

   empno INTEGER,                        

  ename VARCHAR(10) NOT NULL,                

  job VARCHAR(9),                            

  mgr INTEGER,                              

  hiredate DATE,                             

  sal DECIMAL(7,2),                            

  comm DECIMAL(7,2),                        

  deptno INTEGER NOT NULL,               

  CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)

  REFERENCES p_dept (deptno));

 

ORACLE上的FK约束：

CREATE TABLE emp(

    empno NUMBER(4),

   ename VARCHAR2(10) NOT NULL,

   job VARCHAR2(9),

   mgr NUMBER(4),

   hiredate DATE,

   sal NUMBER(7,2),

   comm NUMBER(7,2),

   deptno NUMBER(7,2) NOT NULL,

  CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)

  REFERENCES dept (deptno));

 

DB2上的FK约束：

CREATE TABLE F_emp(                             

   empno INTEGER,                        

  ename VARCHAR(10) NOT NULL,                

  job VARCHAR(9),                            

  mgr INTEGER,                              

  hiredate DATE,                             

  sal DECIMAL(7,2),                            

  comm DECIMAL(7,2),                        

 deptno INTEGER NOT NULL,               

  CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)

  REFERENCES p_dept (deptno));

 

FK约束的几个特点：

1。FOREIGN KEY：在表级定义时需要

2。REFERENCES：指定主表及其主键列

3。ON DELETE CASCADE：级联删除选项

 

SQL SERVER上的CHECK约束：

create table test ( deptno bigint constraint emp_deptno_ck check (deptno

between 10 and 99))

 

ORACLE上的CHECK约束：

 create table test ( deptno number(2) constraint emp_deptno_ck check (deptno

between 10 and 99))

 

DB2 上的CHECK约束：

create table test ( deptno number(2) constraint emp_deptno_ck check (deptno

between 10 and 99))