約束用於強制行數據滿足特定的商業規則(數據類型是強制列的數據滿足規則)
約束有五種類型:
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))