SQL: Data Definition

SQL: Data Definition

1. 7.1 The ISO SQL Data Types

   • 7.1.1 SQL Identifiers
   • 7.1.2 SQL Scalar Data Types
     • Boolean
     • Character
       • CHAR
       • VARCHAR
   • 7.1.3 Exact Numeric Data
     • NUMERIC
     • DECIMAL
     • INTEGER
     • SMALLINT
   • Aproximate numeric data
     • FLOAT
     • REAL
     • DOUBLE PRECISION
   • Datetime data

2. 7.2 Integrity Enhancement Feature

   • Required data
     • When NOT NULL is specified, the system rejects any attempt to insert a null in the column
     • position VARCHAR(10) NOT NULL
   • domain constraints
     • CHECK
       • CHECK (searchCondition)
     • CREATE DOMAIN
       • CREATE DOMAIN DomainName [AS] dataType [DEFAULT defaultOption] [CHECK (searchCondition)]
       • DROP DOMAIN DomainName [Restrict | Cascade]
     • Every column has a domain; in other words, a set of legal values
   • entity integrity
     • The primary key of a table must contain a unique, nonnull value for each row
     • Primary key
       • PRIMARY KEY(propertyNo)
     • Composite Primary Key
       • PRIMARY KEY(clientNo, propertyNo)
     • Uniqueness
       • UNIQUE(clientNo, propertyNo
   • referential integrity
     • If a foreign key contains a value, that value must refer to an existing, valid row in the parent table
     • FOREIGN KEY(branchNo) REFERENCES Branch
   • general constraints
     • Updates tables may be constraint by enterprise rules governing the real-world transactions that are represented by the updates
     • CREATE ASSERTION AssertionName CHECK (searchCondition)

3. Modos de operaciones

   • CASCADE
   • SET NULL
   • SET DEFAULT
   • NO ACTION

4. Data Definition

   • Create
     • Schemas
     • Table
     • Index
   • Change Table Definition
   • Remove
     • Table
     • Index