对比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))
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值