Oracle约束

1、6中约束

NOT NULL、PRIMARY KEY、FOREIGN KEY、CHECK、UNIQUE、DEFAULT

其中UNIQUE可以为NULL

2、CREATE TABLE goods(goodsid CHAR(8) PRIMARY KEY,--主键
             goodsname VARCHAR2(30),
             unitprice NUMBER(10,2) CHECK(unitprice>0),
             CATEGORY  VARCHAR2(8),
             provider VARCHAR2(30));
            
CREATE TABLE customer(customerid CHAR(8) PRIMARY KEY,--主键
                      NAME VARCHAR2(50) NOT NULL,
                      address VARCHAR2(50),
                      email VARCHAR2(50) UNIQUE,
                      sex CHAR(2) DEFAULT '男' check(sex IN('男','女')),
                      cardid CHAR(18));
CREATE TABLE purchase(customerid CHAR(8) REFERENCES customer(customerid),
                      goodsid CHAR(8)    REFERENCES goods(goodsid),
                      nums NUMBER(5) CHECK(nums BETWEEN 1 AND 30),
                      PRIMARY KEY(customerid,goodsid));

3、ALTER TABLE 增加约束

增加NOT NULL约束时使用modify命令
ALTER TABLE goods MODIFY goodsname NOT NULL;

增加其他约束使用ADD命令

ALTER TABLE customer ADD CONSTRAINT cardid_unique  UNIQUE(cardid) ;

ALTER TABLE customer ADD CONSTRAINT address_check  CHECK(address IN('东城','西城','南京','北街')) ;

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;