Domain Constrains

• -- check语法
  check (searchCondition)
  -- example: 
  sex char not null check(set in('M','F'));
  -- 创建自定义域
  CREATE DOMAIN DomainName [AS] dataType
  [DEFAULT defaultOption]
  [CHECK (searchCondition)]
  -- example
  CREATE DOMAIN SexType AS CHAR DEFAULT ‘M’ CHECK (VALUE IN (‘M’, ‘F’));
  CREATE DOMAIN BranchNo AS CHAR(4) CHECK (VALUE IN (SELECT branchNo FROM Branch));
  -- 删除自定义域
  DROP DOMAIN DomainName [RESTRICT | CASCADE]
  -- RESTRICT：如果该域正在被用于某个现存的表、视图或者断言的定义，那么，撤销失败
  -- CASCADE：则任一表中基于该域的列都会自动地变为用该域的基类型定义，同时在合适的情况下，该域的任何约束或者default子句也都会被列约束和列default子句代替
  

Referential Integrity

• FOREIGN KEY (columnList) REFERENCES TableName
  FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL
  -- on update / on delete，如果删除父表中与子表有匹配行的候选关键字
  	-- CASCADE:将子表中对应行也删除
  	-- SET NULL:将子表中的外键设置为空
  	-- SET DEFAULT:将子表中的外键设置成默认值，前提是创建属性时指定了默认值
  	-- NoAction:如果子表中有对应匹配行，则delet操作不被允许
  

Enterprise Constrains

• CREATE ASSERTION AssertionName CHECK (searchCondition)
  

Creating a Database

• CREATE SCHEMA [Name | AUTHORIZATION CreatorId ]
  DROP SCHEMA Name [RESTRICT | CASCADE ]
  

Table

• -- 创建表
  CREATE TABLE TableName
  ({colName dataType [NOT NULL] [UNIQUE]
  [DEFAULT defaultOption]
  [CHECK searchCondition] [,...]}
  [PRIMARY KEY (listOfColumns),]
  {[UNIQUE (listOfColumns),] […,]}
  {[FOREIGN KEY (listOfFKColumns)
  REFERENCES ParentTableName
  [(listOfCKColumns)], -- 如果 listOfColumns省略，则默认匹配到父表的主键
  [ON UPDATE referentialAction]
  [ON DELETE referentialAction ]] [,…]}
  {[CHECK (searchCondition)] [,…] })
  -- example
  CREATE TABLE PropertyForRent (
  propertyNo char NOT NULL,
  rooms char NOT NULL DEFAULT 4,
  rent char NOT NULL DEFAULT 600,
  ownerNo char NOT NULL,
  staffNo char,
  CONSTRAINT StaffNotHandlingTooMuch
  CHECK ( NOT EXISTS ( SELECT staffNo
  FROM propertyForRent
  GROUP BY staffNo
  HAVING COUNT(*)>100)),
  branchNo char NOT NULL,
  PRIMARY KEY (propertyNo),
  FOREIGN KEY (staffNo) REFERENCES Staff
  ON DELETE SET NULL ON UPDATE CASCADE);
  -- 更改表
  ALTER TABLE TabName
  -- 添加属性
  [ADD [COLUMN] columnName dataType
  [NOT NULL][UNIQUE][DEFAULT defaultOption]
  [CHECK (searchCondition)]]
  -- 删除属性
  [DROP [COLUMN] columnName [RESTRICT | CASCADE ]]
  -- 添加限制
  [ADD [CONSTRAINT [constraintName]]
  tableConstraintDefinition]
  -- 删除限制
  [DROP CONSTRAINT constraintName [RESTRICT | CASCADE ]]
  -- 更改属性的默认值
  [ALTER [COLUMN] SET DEFAULT defaultOption]
  [ALTER [COLUMN] DROP DEFAULT]
  -- 删除表
  DROP TABLE TableName [RESTRICT | CASCADE]