约束用于强制行数据满足特定的商业规则(数据类型是强制列的数据满足规则)
约束有五种类型:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
SQL SERVER上的NOT NULL约束:
1. CREATE TABLE U_emp(
2. empno bigint,
3. ename VARCHAR(10) NOT NULL,
4. job VARCHAR(9),
5. mgr bigint,
6. hiredate DATE,
7. sal decimal(7,2),
8. comm decimal(7,2),
9. deptno decimal(7,2) NOT NULL);
ORACLE上的NOT NULL约束:
1. CREATE TABLE emp(
2. empno NUMBER(4),
3. ename VARCHAR2(10) NOT NULL,
4. job VARCHAR2(9),
5. mgr NUMBER(4),
6. hiredate DATE,
7. sal NUMBER(7,2),
8. comm NUMBER(7,2),
9. deptno NUMBER(7,2) NOT NULL);
DB2上的NOT NULL约束:
1. CREATE TABLE U_emp(
2. empno INTEGER,
3. ename VARCHAR(10) NOT NULL,
4. job VARCHAR(9),
5. mgr INTEGER,
6. hiredate DATE,
7. sal DECIMAL(7,2),
8. comm DECIMAL(7,2),
9. deptno DECIMAL(7,2) NOT NULL);
SQL SERVER上的UNIQUE约束:
1. CREATE TABLE U_dept(
2. deptno INTEGER,
3. dname VARCHAR(14),
4. loc VARCHAR(13),
5. CONSTRAINT dept_dname_uk UNIQUE(dname));
ORACLE上的UNIQUE约束:
1. CREATE TABLE dept(
2. deptno NUMBER(2),
3. dname VARCHAR2(14),
4. loc VARCHAR2(13),
5. CONSTRAINT dept_dname_uk UNIQUE(dname));
DB2上的UNIQUE约束:
1. CREATE TABLE U_dept(
2. deptno INTEGER,
3. dname VARCHAR(14) not null,
4. loc VARCHAR(13),
5. CONSTRAINT dept_dname_uk UNIQUE(dname));
SQL SERVER上的PK 约束:
1. CREATE TABLE P_dept(
2. deptno INTEGER,
3. dname VARCHAR(14),
4. loc VARCHAR(13),
5. CONSTRAINT dept_dname_uk1 UNIQUE (dname),
6. CONSTRAINT dept_deptno_pk1 PRIMARY KEY(deptno));
ORACLE上的PK约束
1. CREATE TABLE dept(
2. deptno NUMBER(2),
3. dname VARCHAR2(14),
4. loc VARCHAR2(13),
5. CONSTRAINT dept_dname_uk UNIQUE (dname),
6. CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));
DB2和的PK约束:
1. CREATE TABLE P_dept(
2. deptno INTEGER not null,
3. dname VARCHAR(14) not null,
4. loc VARCHAR(13),
5. CONSTRAINT dept_dname_uk1 UNIQUE (dname),
6. CONSTRAINT dept_deptno_pk1 PRIMARY KEY(deptno));
SQL SERVER上的FK 约束:
1. CREATE TABLE F_emp(
2. empno INTEGER,
3. ename VARCHAR(10) NOT NULL,
4. job VARCHAR(9),
5. mgr INTEGER,
6. hiredate DATE,
7. sal DECIMAL(7,2),
8. comm DECIMAL(7,2),
9. deptno INTEGER NOT NULL,
10. CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11. REFERENCES p_dept (deptno));
ORACLE上的FK约束:
1. CREATE TABLE emp(
2. empno NUMBER(4),
3. ename VARCHAR2(10) NOT NULL,
4. job VARCHAR2(9),
5. mgr NUMBER(4),
6. hiredate DATE,
7. sal NUMBER(7,2),
8. comm NUMBER(7,2),
9. deptno NUMBER(7,2) NOT NULL,
10. CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11. REFERENCES dept (deptno));
DB2上的FK约束:
1. CREATE TABLE F_emp(
2. empno INTEGER,
3. ename VARCHAR(10) NOT NULL,
4. job VARCHAR(9),
5. mgr INTEGER,
6. hiredate DATE,
7. sal DECIMAL(7,2),
8. comm DECIMAL(7,2),
9. deptno INTEGER NOT NULL,
10. CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11. REFERENCES p_dept (deptno));
FK约束的几个特点:
1.FOREIGN KEY:在表级定义时需要
2.REFERENCES:指定主表及其主键列
3.ON DELETE CASCADE:级联删除选项
SQL SERVER上的CHECK约束:
1. create table test ( deptno bigint constraint emp_deptno_ck check (deptno
2. between 10 and 99))
ORACLE上的CHECK约束:
1. create table test ( deptno number(2) constraint emp_deptno_ck check (deptno
2. between 10 and 99))
DB2 上的CHECK约束:
1. create table test ( deptno number(2) constraint emp_deptno_ck check (deptno
2. between 10 and 99))
来源:网络 编辑:联动北方技术论坛