oracle约束

NOT NULL 非空约束
创建
CREATE TABLE TEST3(ID NUMBER NOT NULL, NAME VARCHAR2(10) NOT NULL);
测试
INSERT INTO TEST3 VALUES(1, 'A');  成功
INSERT INTO TEST3 VALUES(1, 'A');  成功
INSERT INTO TEST3 VALUES(NULL, NULL); 失败

UNIQUE：唯一约束
UNIQUE 约束应用于 Id 和 Email 列    这两列将不接受重复值。
CREATE TABLE Employee (
  Id NUMBER UNIQUE,
  Name VARCHAR(10),
  Email VARCHAR(20) UNIQUE,
  Department VARCHAR(10)
);

插入数据
INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'lihao', 'lihao@g.com', 'IT');

ORA-00001: 违反唯一约束条件        将重复的 Id 值插入到 Id 列中
INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'zhangsan', 'zhangsan@g.com', 'IT');

这样就可以了
INSERT INTO Employee (Id, Name, Email, Department) VALUES (2, 'zhangsan', 'zhangsan@g.com', 'IT');

ORA-00001: 违反唯一约束条件        重复的电子邮件值插入到电子邮件列中
INSERT INTO Employee (Id, Name, Email, Department) VALUES (3, 'lisi', 'zhangsan@g.com', 'IT');

UNIQUE允许空值。
INSERT INTO Employee (Id, Name, Email, Department) VALUES (null, 'lisi', 'lisi@g.com', 'IT');

这样也可以
INSERT INTO Employee (Id, Name, Email, Department) VALUES (null, 'wangwu', 'ww@g.com', 'IT');

现有列增加UNIQUE 约束
ALTER TABLE Employee ADD UNIQUE (Name);

创建
CREATE TABLE TEST1(Id INT UNIQUE, Name VARCHAR2(10) UNIQUE);
测试
INSERT INTO TEST1 VALUES (1, 'A');      成功
INSERT INTO TEST1 VALUES (1, 'A');      失败
INSERT INTO TEST1 VALUES (NULL, NULL);  成功

创建
CREATE TABLE Test2
( 
 Id      INT, 
 Name    VARCHAR(50), 
 Email   VARCHAR(10), 
 UNIQUE(Name),
 UNIQUE(Email)
);

测试
INSERT INTO TEST2 VALUES(1,'A', 'A@G.COM'); 成功
INSERT INTO TEST2 VALUES(2,'A', 'B@G.COM'); 失败
INSERT INTO TEST2 VALUES(3,'C', 'A@G.COM'); 失败

创建
CREATE TABLE t3
( 
    Id    INT NOT NULL, 
    Name  VARCHAR(30), 
    Email VARCHAR(50),
    UNIQUE (Name, Email)
);

测试
INSERT INTO t3 (Id, Name, Email) VALUES (1, 'Sambit', 'S@G.COM'); 成功
INSERT INTO t3 (Id, Name, Email) VALUES (2, 'Sambit', 'S1@G.COM'); 成功
INSERT INTO t3 (Id, Name, Email) VALUES (3, 'Sambit1', 'S1@G.COM'); 成功
INSERT INTO t3 (Id, Name, Email) VALUES (4, 'Sambit', 'S@G.COM');  失败

Check检查约束
创建表 测试
ID列，它只允许 100 到 1000 之间的值。
AGE列，它只允许大于等于18的值
DeptID 列，它只允许大于0且小于100的值
CREATE TABLE Employees(
   ID INT CHECK(ID BETWEEN 100 AND 1000),
   NAME VARCHAR(20),
   AGE  INT NOT NULL CHECK (AGE >= 18),
   DeptID INT CHECK(DeptID > 0 AND DeptID < 100),
   SALARY NUMBER(10)
);

INSERT INTO Employees values (100, 'Anurag', 20, 1, 20000);      成功
INSERT INTO Employees values (101, 'Mohanty', 25, 2, 25000);     成功
INSERT INTO Employees values (10, 'Sambit', 20, 1, 20000);       失败在ID
INSERT INTO Employees values (102, 'Sambit', 10, 1, 20000);      失败在AGE
INSERT INTO Employees values (102, 'Sambit', 20, 105, 20000);    失败在DeptID

创建表 测试        对PersonID、AGE和DeptID 列应用了三个查检约束。
CREATE TABLE Persons(
   PersonID INT,
   NAME VARCHAR(20),
   AGE  INT,
   DeptID INT,
   SALARY NUMBER(10),
   CONSTRAINT CONSTRAINT_PersonID CHECK(PersonID BETWEEN 100 AND 1000),
   CONSTRAINT CONSTRAINT_AGE CHECK(AGE >= 18),
   CONSTRAINT CONSTRAINT_DeptID CHECK(DeptID > 0 AND DeptID < 100)
);

INSERT INTO Persons VALUES (101, 'Anurag', 20, 10, 1000);  成功
INSERT INTO Persons VALUES (10, 'Anurag', 20, 10, 1000);   失败
INSERT INTO Persons VALUES (102, 'Anurag', 15, 10, 1000);  失败
INSERT INTO Persons VALUES (102, 'Anurag', 20, 105, 1000); 失败

创建表 测试        对AGE和DeptID列应用CHECK约束
CREATE TABLE Person(
   PersonID INT,
   NAME VARCHAR(20),
   AGE  INT,
   DeptID INT,
   SALARY NUMBER(10),
   CONSTRAINT CONSTRAINT_AGE_DepTID CHECK(AGE >= 18 AND DepTID > 0)
);

INSERT INTO Person VALUES (1, 'Anurag', 20, 10, 1000);  成功
INSERT INTO Person VALUES (2, 'Anurag', 15, 20, 1000);  失败
INSERT INTO Person VALUES (3, 'Anurag', 22, 0, 1000);   失败
INSERT INTO Person VALUES (1, 'Anurag', 15, 0, 1000);   失败

PRIMARY KEY主键约束
一张表只能有一个主键    ORA-02260: 表只能具有一个主键
CREATE TABLE Employee 
( 
    Id   INT PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Email  VARCHAR2(20) PRIMARY KEY,
    Department  VARCHAR2(10)
);

Id列标记为主键
CREATE TABLE Employee 
( 
    Id    NUMBER PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Email  VARCHAR2(20),
    Department  VARCHAR2(10)
);

在主键列中添加重复值：    ORA-00001: 违反唯一约束条件
INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'lihao', 'lihao@g.com', 'IT');
INSERT INTO Employee (Id, Name, Email, Department) VALUES (1, 'zhangsan', 'zs@g.com', 'IT');

主键列中插入NULL    ORA-01400: 无法将 NULL 插入        主键列不接受NULL
INSERT INTO Employee (Id, Name, Email, Department) VALUES (null, 'zhangsan', 'zs@g.com', 'IT');

创建带主键的表
CREATE TABLE Student
( 
    Id      INT, 
    Name    VARCHAR2(20), 
    Email   VARCHAR2(20),
    PRIMARY KEY(Id)
);

创建带主键的表
CREATE TABLE StudentDetails
( 
    Id      INT, 
    NAME    VARCHAR2(20), 
    Email   VARCHAR2(30),
    CONSTRAINT Student_Id_Primary PRIMARY KEY (Id)
);

复合主键    在多个列上创建主键约束
CREATE TABLE EmployeeDetails 
( 
    Id   NUMBER, 
    Name VARCHAR2(20), 
    Email  VARCHAR2(20), 
    PRIMARY KEY(Name, Email) 
);

INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (1, 'aaa', 'aaa@g.com');   成功
INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (2, 'aaa', 'aaa1@g.com');  成功
INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (3, 'aaa1', 'aaa1@g.com');  成功
INSERT INTO EmployeeDetails (Id, Name, Email) VALUES (1, 'aaa', 'aaa@g.com');   失败

现有表添加 PRIMARY KEY 
CREATE TABLE Test
( 
    Id      INT, 
    NAME    VARCHAR2(20), 
    Email VARCHAR2(20)
);

ALTER TABLE Test ADD PRIMARY KEY (Id);

删除表的主键约束
ALTER TABLE Test DROP PRIMARY KEY;

REFERENCES外键约束
创建主表
CREATE TABLE Department
( 
    Id   INT PRIMARY KEY, 
    Name VARCHAR2(20), 
    Location  VARCHAR2(20) 
);
插入数据
INSERT INTO Department (Id, Name, Location) VALUES (10, 'IT', '3ceng');
INSERT INTO Department (Id, Name, Location) VALUES (20, 'HR', 'b1');
INSERT INTO Department (Id, Name, Location) VALUES (30, 'FO', '1ceng');

创建外键表  子表  用外键约束创建Employee表
CREATE TABLE Employee 
( 
    ID    INT PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Salary INT, 
    DepartmentId INT REFERENCES Department(Id) 
);

插入数据
INSERT into Employee VALUES (101, 'lihao', 25000, 10); 
INSERT into Employee VALUES (102, 'zhangsan', 32000, 20); 
INSERT into Employee VALUES (103, 'lisi', 35000, 30);

ORA-02291: 违反完整约束条件 未找到父项关键字
Department Id值传递为 40，这在 Department 表中实际上并不存在。
INSERT into Employee VALUES (104, 'wangwu', 52000, 40);

ORA-02292: 违反完整约束条件 已找到子记录
父表的引用键值在子表中有相应的子记录，无法更新父表的引用键值
UPDATE Department SET Id = 100 WHERE Id = 10;

ORA-02292: 违反完整约束条件 已找到子记录
DELETE FROM Department WHERE Id = 10;

给已有表添加外键约束
在DepartmentId列上添加一个外键，该外键应引用 Department 表的 Id 列
CREATE TABLE Employee1 
( 
    ID    INT PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Salary INT, 
    DepartmentId INT
);

ALTER TABLE Employee1 ADD FOREIGN KEY (DepartmentId) REFERENCES Department(Id);

创建了所有列，然后在创建了FOREIGN KEY约束
CREATE TABLE Employee2 
( 
    ID    INT PRIMARY KEY, 
    Name  VARCHAR2(20), 
    Salary INT, 
    DepartmentId INT,
    FOREIGN KEY (DepartmentId) REFERENCES Department(Id)  
);

完整性约束，创建测试表
部门表（主键表）
CREATE TABLE Department( 
    Id   INT PRIMARY KEY, 
    Name VARCHAR2(10) 
);
Insert into Department1 values (10, 'IT');
Insert into Department1 values (20, 'HR');
Insert into Department1 values (30, 'FO');

雇员表（外键表）
CREATE TABLE Employees1(
  Id INT PRIMARY KEY,
  Name VARCHAR2(20) NOT NULL,
  DepartmentID INT REFERENCES Department1(Id)
);
INSERT into Employees1 VALUES (101, 'zs', 10); 
INSERT into Employees1 VALUES (102, 'ls', 20); 
INSERT into Employees1 VALUES (103, 'ww', 30);

无法删除
DELETE FROM Department1 WHERE Id = 10;

重新创建创建删除级联
ON DELETE CASCADE：每当我们从父表中删除一条记录时，相关的子记录就会自动从子表中删除。
删除表，重新创建
DROP TABLE Employees1;

CREATE TABLE Employees1(
  Id INT PRIMARY KEY,
  Name VARCHAR2(20) NOT NULL,
  DepartmentID INT REFERENCES Department1(Id)
  ON DELETE CASCADE 
);

INSERT into Employees1 VALUES (101, 'zs', 10);
INSERT into Employees1 VALUES (102, 'ls', 20);
INSERT into Employees1 VALUES (103, 'ww', 30);

测试删除主表，子表内容没有了
SELECT * FROM Employees1;
删除和查看 
DELETE FROM Department1 WHERE Id = 10;
SELECT * FROM Employees1;

                                查询约束
创建测试表
CREATE TABLE Dep( 
    Id   INT PRIMARY KEY, 
    Name VARCHAR2(10) 
);

CREATE TABLE Emp(
  Id INT PRIMARY KEY,
  Name VARCHAR2(20) NOT NULL,
  Email VARCHAR2(20) UNIQUE,
  Age INT NOT NULL CHECK (AGE >= 18),
  DepartmentID INT REFERENCES Dep(Id)
);

查找表的所有约束名称和类型
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS
WHERE TABLE_NAME='EMP';

查找表的所有约束名称和列名称
SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS 
WHERE TABLE_NAME='EMP';

查找NOT NULL约束的逻辑条件
SELECT SEARCH_CONDITION FROM USER_CONSTRAINTS 
WHERE TABLE_NAME='EMP';

查找表的所有列信息
SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME='EMP';

查找表中的列数
SELECT COUNT(*) FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME='EMP';

C表示CHECK约束，P表示Primary Key，U表示unique约束
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS 
WHERE TABLE_NAME= 'EMP' ;

创建时指定约束名称        查询约束名称
CREATE TABLE TESTEMP2(
  Id INT CONSTRAINT PK_ID PRIMARY KEY,
  Email VARCHAR2(20) CONSTRAINT UK_EMAIL UNIQUE,
  Age INT CONSTRAINT CHECK_AGE CHECK (AGE >= 18)
);

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS 
WHERE TABLE_NAME='TESTEMP2';

CREATE TABLE TESTEMP3(
  Id INT,
  Email VARCHAR2(20), 
  Age INT,
  CONSTRAINT PK_ID1 PRIMARY KEY (Id),
  CONSTRAINT UK_EMAIL1 UNIQUE (Email),
  CONSTRAINT CHECK_AGE1 CHECK (AGE >= 18)
);
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS 
WHERE TABLE_NAME='TESTEMP3';

                        添加和删除约束
创建测试表
CREATE TABLE Dep2
( 
    Id   INT, 
    Name VARCHAR2(20), 
    Location  VARCHAR2(20) 
);

CREATE TABLE Employee1 
( 
    ID    INT, 
    Name VARCHAR2(20), 
    Email VARCHAR2(20),
    AGE INT, 
    DepartmentId INT
); 

Id列上添加主键
ALTER TABLE Dep2 ADD CONSTRAINT PK_ID2 PRIMARY KEY(Id);
ALTER TABLE Emp2 ADD CONSTRAINT PK_ID3 PRIMARY KEY(Id);

添加唯一约束
ALTER TABLE Emp2 ADD CONSTRAINT UQ_Email UNIQUE(Email);

添加检查约束
ALTER TABLE Emp2 ADD CONSTRAINT CHK_AGE CHECK(Age>= 18);

添加非空约束
ALTER TABLE Emp2 MODIFY Name CONSTRAINT NN_NAME NOT NULL;

添加外键约束
ALTER TABLE Emp2 ADD CONSTRAINT FK_DEPARTMENTID 
FOREIGN KEY(DepartmentId) REFERENCES Dep2(Id) ON DELETE CASCADE;

查看增加的约束
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS
WHERE TABLE_NAME= 'EMP2';

删除主键
ALTER TABLE Emp2 DROP CONSTRAINT PK_ID3;

删除外键
ALTER TABLE Emp2 DROP CONSTRAINT FK_DEPARTMENTID;

删除唯一、检查、非空约束
ALTER TABLE Emp2 DROP CONSTRAINT CHK_AGE;  
ALTER TABLE Emp2 DROP CONSTRAINT UQ_EMAIL;
ALTER TABLE Emp2 DROP CONSTRAINT NN_NAME;

                            禁用和启用约束
正常，没问题
CREATE TABLE TEST5
(
Name VARCHAR2(10), 
Salary NUMBER (10) CONSTRAINT CHK_SALARY CHECK (Salary >= 5000)
);
INSERT INTO TEST5 VALUES ('lihao', 9000);

无法插入        需要先禁用一下
INSERT INTO TEST5 VALUES ('Sambit', 3000);
ALTER TABLE TEST5 DISABLE CONSTRAINT CHK_SALARY;
INSERT INTO TEST5 VALUES ('Sambit', 3000);

启用约束
ALTER TABLE TEST5 ENABLE CONSTRAINT CHK_SALARY;  不能这样启用
ALTER TABLE TEST5 ENABLE NOVALIDATE CONSTRAINT CHK_SALARY;