对比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 (deptnobetween 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))