Data Definition

1

• Data Definition

2
Data Definition

• SQL DDL allows database objects such as tables,
  views, and indexes to be created and destroyed.
• Main SQL DDL statements are
• CREATE/ALTER TABLE DROP TABLE
• CREATE VIEW DROP VIEW
• CREATE INDEX DROP INDEX

3
Requirements to Create Table

• To create a relational table in your own schema,
  you must have the CREATE TABLE system privilege.
  To create a table in another users schema, you
  must have CREATE ANY TABLE system privilege.
  Also, the owner of the schema to contain the
  table must have either space quota on the
  tablespace to contain the table or UNLIMITED
  TABLESPACE system privilege.

4
CREATE TABLE

• CREATE TABLE TableName
• (colName dataType NOT NULL UNIQUE
• DEFAULT defaultOption
• CHECK searchCondition ,...
• PRIMARY KEY (listOfColumns),
• UNIQUE (listOfColumns), ,
• FOREIGN KEY (listOfFKColumns)
• REFERENCES ParentTableName (listOfCKColumns),
• ON UPDATE referentialAction
• ON DELETE referentialAction ,
• CHECK (searchCondition) , )

5
Oracle Data Types

• DATATYPE DESCRIPTION RANGE
• CHAR Fixed-length character strings 1-2,000
  Bytes
• VARCHAR andVARCHAR2 Variable-length character
  strings
• (recommended to use VARCHAR2) Up To 4,000
  Bytes
• LONG Variable-length character strings Up To
  2 GB
• NUMBER (P, S) PPrecision (Total of Digits),
  SScale (Where Rounding Occurs) Up To 21 Bytes
• NCHAR National Language Fixed-Length Character
  Field with Trailing Blanks Up To 2,000 Bytes
• NVARCHAR2 National Language Variable- Length
  Character Field Up To 4,000 Bytes
• DATE Fixed-Length Date and Time Field
• BOOLEAN True/False

6
CREATE TABLE

• Example
• CREATE TABLE person (
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE,
• sex CHAR
• weight NUMBER(3,2)
• )
• Can specify a DEFAULT value for the column.
• Can specify a constraint

7
Constraints

• Are used to define an integrity constraint
• Are rules that restricts the values in a
  database.
• Five types of integrity constraints
• Required data.
• Domain constraints.
• Entity integrity.
• Referential integrity.
• Enterprise constraints.

8
Integrity Constraints

• Some of the constraints supported by Oracle
• NOT NULL constraint
• prohibits values to be null.
• unique constraint
• prohibits same values (excluding nulls)
• primary key constraint
• combines a NOT NULL and a UNIQUE constraints.
• foreign key constraint
• requires values in one table to match values in
  another table.
• check constraint
• requires a value in the database to comply with a
  specified condition.

9
Integrity Constraints

• You can define constraints syntactically in two
  ways
• As part of the definition of an individual column
  or attribute. This is called inline
  specification.
• As part of the table definition. This is called
  out-of-line specification.
• NOT NULL constraints must be declared inline. All
  other constraints can be declared either inline
  or out of line.

10
NOT NULL

• If you specify neither NOT NULL nor NULL, then
  the default is NULL.
• Restrictions on NOT NULL Constraints
• You cannot specify NULL or NOT NULL in a view
  constraint.
• You cannot specify NULL or NOT NULL for an
  attribute of an object. Instead, use a CHECK
  constraint with the IS NOT NULL condition.

11
EXAMPLE

• CREATE TABLE person (
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE NOT NULL,
• sex CHAR
• weight NUMBER(3,2)
• )

12
UNIQUE

• A unique constraint designates a column as a
  unique key (a candidate key).
• When you define a unique constraint inline, you
  need only the UNIQUE keyword.
• When you define a unique constraint out of line,
  you must also specify one or more columns.
• You must define a composite unique key out of
  line.
• Unique key made up of a single column can contain
  nulls.

13
Example

• CREATE TABLE person (
• person_id Number
• CONSTRAINT p_id_constraint UNIQUE,
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE NOT NULL,
• sex CHAR,
• weight NUMBER(3,2)
• )

14
Another Example

• CREATE TABLE person (
• person_id Number ,
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE NOT NULL,
• sex CHAR,
• weight NUMBER(3,2),
• CONSTRAINT p_id_constraint UNIQUE
  (first_name, last_name)
• )

15
Another Example

• CREATE TABLE person (
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE NOT NULL,
• sex CHAR,
• weight NUMBER(3,2),
• UNIQUE (first_name, last_name)
• )

16
Restrictions on Unique Constraints

• A table or view can have only one unique key.
• There is restriction on datatypes that can be
  declared unique.
• A composite unique key cannot have more than 32
  columns.
• You cannot designate the same column or
  combination of columns as both a primary key and
  a unique key.

17
PRIMARY KEY

• When you define a primary key constraint inline,
  you need only the PRIMARY KEY keywords.
• When you define a primary key constraint out of
  line, you must also specify one or more columns.
  You must define a composite primary key out of
  line.
• No column that is part of the primary key can
  contain a null.

18
Example

• CREATE TABLE person (
• person_id NUMBER
• CONSTRAINT per_id_const PRIMARY KEY,
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE NOT NULL,
• sex CHAR,
• weight NUMBER(3,2),
• UNIQUE (first_name, last_name)
• )

19
Another Example

• CREATE TABLE person (
• person_id NUMBER ,
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE NOT NULL,
• sex CHAR
• weight NUMBER(3,2),
• CONSTRAINT per_id_const PRIMARY KEY
  (first_name, last_name)
• )

20
Another Example

• CREATE TABLE person (
• person_id NUMBER ,
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE NOT NULL,
• sex CHAR
• weight NUMBER(3,2),
• PRIMARY KEY (first_name, last_name)
• )

21
Restrictions on Primary Key Constraints

• A table or view can have only one primary key.
• None of the columns in the primary key can have
  datatype
• LOB, LONG, LONG, RAW, VARRAY, NESTED TABLE,
  OBJECT, BFILE, or REF, or TIMESTAMP WITH TIME
  ZONE. However, the primary key can contain a
  column of TIMESTAMP WITH LOCAL TIME ZONE.
• A composite primary key cannot have more than 32
  columns.
• You cannot designate the same column or
  combination of columns as both a primary key and
  a unique key.

22
FOREIGN KEY(referential integrity constraint)

• A foreign key constraint establishes a
  relationship between that foreign key and a
  specified primary or unique key, called the
  referenced key.
• A composite foreign key designates a combination
  of columns as the foreign key.
• The table or view containing the foreign key is
  called the child object, and the table or view
  containing the referenced key is called the
  parent object.

23
FOREIGN KEY

• If you identify only the parent table or view and
  omit the column name, then the foreign key
  automatically references the primary key of the
  parent table or view.
• The corresponding column or columns of the
  foreign key and the referenced key must match in
  order and datatype.
• You can define a foreign key constraint on a
  single key column either inline or out of line.
• You must specify a composite foreign key and a
  foreign key on an attribute out of line.

24
FOREIGN KEY

• You can designate the same column or combination
  of columns as both a foreign key and a primary or
  unique key. You can also designate the same
  column or
• You can define multiple foreign keys in a table
  or view.
• Also, a single column can be part of more than
  one foreign key.

25
Example

• CREATE TABLE dept_20(
• employee_id NUMBER(4),
• last_name VARCHAR2(10),
• job_id VARCHAR2(9),
• manager_id NUMBER(4),
• hire_date DATE,
• salary NUMBER(7,2),
• commission_pct NUMBER(7,2),
• department_id,
• CONSTRAINT fk_deptno
• FOREIGN KEY (department_id)
• REFERENCES departments(department_id)
• )

26
Example

• CREATE TABLE dept_20 (
• employee_id NUMBER(4),
• last_name VARCHAR2(10),
• job_id VARCHAR2(9),
• manager_id NUMBER(4),
• hire_date DATE,
• salary NUMBER(7,2),
• commission_pct NUMBER(7,2),
• department_id CONSTRAINT fk_deptno
• REFERENCES departments(department_id)
• )

27
REFRENNCES CLAUSE

• When you specify a foreign key constraint inline,
  you need only the references_clause.
• When you specify a foreign key constraint out of
  line, you must also specify the FOREIGN KEY
  keywords and one or more columns.

28
Example

• CREATE TABLE person (
• parent_id NUMBER
• REFERENCES parent_table (id),
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE NOT NULL,
• sex CHAR
• weight NUMBER(3,2)
• UNIQUE (first_name, last_name)
• )

29
Restrictions on Foreign Key Constraints

• None of the columns in the foreign key can have
  datatype
• LOB, LONG, LONG RAW, VARRAY, NESTED TABLE,
  OBJECT, BFILE, or REF, or TIMESTAMP WITH TIME
  ZONE. However, the primary key can contain a
  column of TIMESTAMP WITH LOCAL TIME ZONE.
• The referenced unique or primary key constraint
  on the parent table or view must already be
  defined.
• A composite foreign key cannot have more than 32
  columns.
• The child and parent tables must be on the same
  database.

30
ON DELETE CLAUSE

• lets you determine how Oracle automatically
  maintains referential integrity if you remove a
  referenced primary or unique key value. If you
  omit this clause, then Oracle does not allow you
  to delete referenced key values in the parent
  table that have dependent rows in the child
  table.
• Specify CASCADE if you want Oracle to remove
  dependent foreign key values.
• Specify SET NULL if you want Oracle to convert
  dependent foreign key values to NULL.

31
Example

• CREATE TABLE dept_20 (
• employee_id NUMBER(4) PRIMARY KEY,
• last_name VARCHAR2(10),
• job_id VARCHAR2(9),
• manager_id NUMBER(4)
• CONSTRAINT fk_mgr REFERENCES employees
• ON DELETE SET NULL,
• hire_date DATE,
• salary NUMBER(7,2),
• commission_pct NUMBER(7,2),
• department_id NUMBER(2)
• CONSTRAINT fk_deptno REFERENCES
  departments(department_id)
• ON DELETE CASCADE
• )

32
CHECK

• lets you specify a condition that each row in the
  table must satisfy. To satisfy the constraint,
  each row in the table must make the condition
  either TRUE or unknown (due to a null).
• The syntax for inline and out-of-line
  specification of check constraints is the same.
• Inline specification can refer only to the column
  currently being defined
• Out-of-line specification can refer to multiple
  columns or attributes.

33
Example

• CREATE TABLE person (
• first_name VARCHAR2(15),
• last_name VARCHAR2(15),
• birthDate DATE NOT NULL,
• sex CHAR
• age NUMBER(3,2) CHECK ( age BETWEEN 18 AND 65)
• )

34
Another Example

• CREATE TABLE divisions(
• div_no NUMBER
• CONSTRAINT check_divno CHECK(div_no BETWEEN 10
  AND 99),
• div_name VARCHAR2(9)
• CONSTRAINT check_divname
• CHECK (div_name UPPER(div_name)),
• office VARCHAR2(10)
• CONSTRAINT check_office
• CHECK (office IN (DALLAS,BOSTON,
• PARIS,TOKYO))
• )

35
Another Example

• CREATE TABLE dept_20 (
• employee_id NUMBER(4) PRIMARY KEY,
• last_name VARCHAR2(10),
• job_id VARCHAR2(9),
• manager_id NUMBER(4),
• salary NUMBER(7,2),
• commission_pct NUMBER(7,2),
• department_id NUMBER(2),
• CONSTRAINT check_sal CHECK (salary
  commission_pct lt 5000)
• )

36
ALTER TABLE

• Add a new column to a table.
• Drop a column from a table.
• Add a new table constraint.
• Drop a table constraint.
• Set a default for a column.
• Drop a default for a column.

37
Prerequisites

• The table must be in your own schema, or you must
  have ALTER privilege on the table, or you must
  have ALTER ANY TABLE system privilege.
• If you are not the owner of the table, then you
  need the DROP ANY TABLE privilege in order to use
  the drop_table_partition or truncate_table_partiti
  on clause.

38
Adding a Table Column

• The following statement adds a column named
  duty_pct of datatype NUMBER and a column named
  visa_needed of datatype VARCHAR2 with a size of 3
  (to hold "yes" and "no" data) and a CHECK
  integrity constraint
• ALTER TABLE countries
• ADD (duty_pct NUMBER(2,2) CHECK (duty_pct lt
  10.5),
• visa_needed VARCHAR2(3))

39
Modifying Table Columns

• The following statement increases the size of the
  duty_pct column
• ALTER TABLE countries
• MODIFY (duty_pct NUMBER(3,2))

40
More examples

• ALTER TABLE sales
• ADD CONSTRAINT sales_pk PRIMARY KEY
  (prod_id,cust_id)
• ALTER TABLE locations_demo
• MODIFY (country_id CONSTRAINT country_nn NOT
  NULL)

41
Specifying Default Column Value

• The following example modifies the min_price
  column of the product_information table so that
  it has a default value of 10
• ALTER TABLE product_information
• MODIFY (min_price DEFAULT 10)
• If you subsequently add a new row to the
  product_information table and do not specify a
  value for the min_price column, then the value of
  the min_price column is automatically 10

42
Discontinue Default Values

• To discontinue default values, use MODIFY to
  replace the values with nulls.
• Example
• ALTER TABLE product_information
• MODIFY (min_price DEFAULT NULL)

43
Dropping Constraints

• The following example drops the primary key of
  the departments table
• ALTER TABLE departments
• DROP PRIMARY KEY CASCADE
• Or, if the the name of the PRIMARY KEY constraint
  is pk_dept
• ALTER TABLE departments
• DROP CONSTRAINT pk_dept CASCADE
• The CASCADE clause drops any foreign keys that
  reference the primary key.

44
Renaming Column

• The following example renames the credit_limit
  column of the sample table customer to
  credit_amount
• ALTER TABLE customers
• RENAME COLUMN credit_limit TO credit_amount

45
Dropping a Column

• Assume table t1 is created as follows
• CREATE TABLE t1 (
• pk NUMBER PRIMARY KEY,
• fk NUMBER,
• c1 NUMBER,
• c2 NUMBER,
• CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1,
• CONSTRAINT ck1 CHECK (pk gt 0 and c1 gt 0),
• CONSTRAINT ck2 CHECK (c2 gt 0)
• )
• ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS
• This statement drops column pk, the primary key
  constraint, the foreign key constraint, ri, and
  the check constraint, ck1

46
Dropping a Column

• If all columns referenced by the constraints
  defined on the dropped columns are also dropped,
  then CASCADE CONSTRAINTS is not required.
• ALTER TABLE t1 DROP (pk, fk, c1)

47
Dropping a Column

• However, the next two statements return errors
• ALTER TABLE t1 DROP (pk)
• pk is a parent key
• ALTER TABLE t1 DROP (c1)
• c1 is referenced by multicolumn constraint ck1

48
Renaming Table

• The following statement renames a table
• ALTER TABLE employees RENAME TO employee
• Warning the impact of renaming a table can be
  very dramatic.
• While Oracle automatically updates the foreign
  keys, constraints definitions, and table
  relationships in the data dictionary, it does not
  update stored code modules in the database,
  stored reports or queries, or client applications

49
DROP TABLE

• DROP TABLE TableName RESTRICT CASCADE
• Example
• DROP TABLE PropertyForRent
• Removes named table and all rows within it.
• With RESTRICT, if any other objects depend for
  their existence on continued existence of this
  table, SQL does not allow request.
• With CASCADE, SQL drops all dependent objects
  (and objects dependent on these objects).

50
Implicit Type Conversion Examples

• Text Literal Example The text literal 10 has
  datatype CHAR. Oracle implicitly converts it to
  the NUMBER data type if it appears in a numeric
  expression as in the
• following statement
• SELECT salary 10
• FROM employees

51
Implicit Type Conversion Examples

• Character and Number Values Example When a
  condition compares a character value and a NUMBER
  value, Oracle implicitly converts the character
  value to a NUMBER value, rather than converting
  the NUMBER value to a character value. In the
  following statement, Oracle implicitly converts
  200 to 200. Assuming that employee_id is a
  numeric type.
• SELECT last_name
• FROM employees
• WHERE employee_id 200

52
Implicit Type Conversion Examples

• Date Example In the following statement, Oracle
  implicitly converts 03-MAR-97 to a DATE value
  using the default date format DD-MON-YY
• SELECT last_name
• FROM employees
• WHERE hire_date 03-MAR-97