Introduction to SQL Creating Tables 2012022 Slide 1

Introduction to SQL Creating Tables 2/01/2022 Slide : 1

An Oracle 7 database can contain multiple data structures Data Structures Table View more Sequence Index Stores data Subset of data from one or tables Generate PK values Improves the performance Define the structures in the database design 2/01/2022 Slide : 2

Table Structures 2/01/2022 Slide : 3 Can be created at any time Do not need a predetermined size Can be modified online

You must have specific priviliges: CREATE TABLE Creating Tables 2/01/2022 Slide : 4 The SYNTAX is as follows; CREATE TABLE table_name (column_name datatype [Default expr] [column_constraint] …… [table_constraint]);

Referencing Tables 2/01/2022 Slide : 5 Constraints must reference tables in the same database Tables belonging to other users are not in the user’s schema You should use the owner’s name as a prefix to the table

The DEFAULT option 2/01/2022 Slide : 6 Specify a default value for a column during an insert Legal values are literal value, expression, or SQL function such as SYSDATE or USER Illegal values are another column name or a pseudocolumn

Must begin with a letter Naming Rules Can be 30 characters long Must contain A-Z, a-z, 0 -9, _, $, and # Must not duplicate the name of another object owned by the same user Must not be an Oracle 7 reserved word 2/01/2022 Slide : 7

Enforce rules at the table level Prevent the deletion of a table if there are dependencies Constraints 2/01/2022 Slide : 8 Constraint types are as follows; NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY CHECK

Constraint Guidelines Name a constraint or the Server can generate a name using the SYS_Cn format Create a constraint At the same time as the table is created After the table has been created Define a constraint at the column or table level 2/01/2022 Slide : 9

Column constraint level Constraint: Syntax 2/01/2022 Slide : 10 column [CONSTRAINT constraint_name] constraint_type Table constraint level column, … [CONSTRAINT constraint_name] constraint_type (column, …)

The NOT NULL constraint 2/01/2022 Slide : 11 Ensures that null values are not permitted for the column Is defined at the column-constraint level

The UNIQUE constraint Designates a column or combination of columns so that no two rows in the table can have the same value for this key Allows null values if the UNIQUE key is based on a single column Is defined at either the table / column level Automaticaly creates UNIQUE index 2/01/2022 Slide : 12

The PRIMARY KEY constraint Creates a primary key for the table; only one primary key is allowed for each table Enforces uniqueness of columns Does not allow null values in any part of the primary key Is defined at either column / table level Automatically creates a UNIQUE index 2/01/2022 Slide : 13

The FOREIGN KEY constraint 2/01/2022 Slide : 14 Designates a column or combination of columns as a foreign key Establishes a relationship between the primary or unique key in the same table or between tables Is defined at either column / table level Must match existing value or be NULL

FOREIGN KEY Keywords Deines the column in the child table at the table constraint level REFERENCES Identifies the table&column in the parent table ON DELETE CASCADE Allows deletion in the parent table and deletion of the dependent rows in the child table 2/01/2022 Slide : 15

Defines a condition that each row must satisfy The CHECK constraint Expressions not allowed References to pseudeo columns CURRVAL, NEXTVAL Calls to SYSDATE, UID, USER Queries that refer to other values in other rows Is defined at either column / table level 2/01/2022 Slide : 16