oracle约束

约束：not null、unique,primary key,foreign key,和check default六个

CREATE TABLE job(job_id NUMBER PRIMARY KEY,NAME VARCHAR2(100) NOT NULL);

CREATE TABLE rsz(ID NUMBER PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL,
tel NUMBER(11) UNIQUE,
sex CHAR(2) CHECK(sex IN('男','女')),
job_id NUMBER REFERENCES job(job_id)
);
--测试约束,ctrl+y删除
INSERT INTO rsz VALUES(3,",12345678903,'女');
COMMIT;
SELECT * FROM rsz;
INSERT INTO rsz(ID,tel,sex,job_id) VALUES(6,13793095559,'女',2);
COMMIT;
SELECT * FROM rsz;
--删除主键
ALTER TABLE rsz DROP PRIMARY KEY;
--添加主键
DELETE FROM rsz;
ALTER TABLE rsz ADD PRIMARY KEY(ID);
--设置字段不可以为null
ALTER TABLE rsz MODIFY NAME NOT NULL;
--设置字段可以为null
ALTER TABLE rsz MODIFY NAME NULL;
--设置不可以重复
ALTER TABLE rsz DROP UNIQUE(tel);
ALTER TABLE rsz MODIFY tel UNIQUE;
ALTER TABLE rsz ADD address VARCHAR2(20) UNIQUE;
--check操作
ALTER TABLE rsz ADD score NUMBER(1,3) CHECK(score BETWEEN 0 AND 99);
--修改/删除默认值
ALTER TABLE rsz MODIFY BODY Default(NULL);
--添加、删除外键
... ... ...
--给约束起名字-constraint
DROP TABLE rsz;
CREATE TABLE rsz(ID NUMBER(38) CONSTRAINT PK_ID PRIMARY KEY,NAME VARCHAR2(20));
ALTER TABLE rsz DROP CONSTRAINT PK_ID;
DELETE FROM rsz;
ALTER TABLE rsz ADD CONSTRAINT PK_ID PRIMARY KEY(ID);
INSERT INTO rsz VALUES(1,'asdf');
COMMIT;
--外键约束名字
DROP TABLE rsz;
CREATE TABLE rsz(ID NUMBER,NAME VARCHAR2(20),job_id NUMBER CONSTRAINT FK_ID REFERENCES job(job_id));
ALTER TABLE rsz DROP CONSTRAINT FK_ID1;
ALTER TABLE rsz ADD CONSTRAINT FK_ID FOREIGN KEY(job_id) REFERENCES job(job_id);