1 / 33

Creating Tables

Creating Tables. Objectives. Create a table containing integrity constraints. Identify table naming conventions. Describe the datatypes that can be used when specifying column definitions. Recognize the indexes that are created automatically by constraints.

barb
Download Presentation

Creating Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating Tables

  2. Objectives • Create a table containing integrity constraints. • Identify table naming conventions. • Describe the datatypes that can be used when specifying column definitions. • Recognize the indexes that are created automatically by constraints. • Create a table by populating it with rows from another table.

  3. Database Objects • An Oracle database can contain multiple data structures. • Table Basic unit of data storage • View Subset of data from one or more tables • Sequence Generates primary key values • Index Improves the performance of some queries • Synonym Gives alternative names to objects • Define the structures in the database design.

  4. Naming Rules • Must begin with a letter • Can be 1–30 characters long • Must contain only 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 Server reserved word

  5. Creating Tables: Syntax CREATE TABLE [schema.]table (column datatype [DEFAULT expr][, …]); You specify: • Table name • Column name, column datatype and size • Schema – same as the owners name • DEFAULT expr – specify the default value if the value is omitted in the INSERT statement

  6. Referencing Another User's Tables • 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. • A schema is a collection of objects including tables, views, sequences, synonyms …

  7. The DEFAULT Option • Specify a default value for a column during an insert. ... hiredate DATE DEFAULT SYSDATE,... • Legal values are literal value, expression, or SQL function such as SYSDATE or USER. • Illegal values are another column name or a pseudocolumn such as NEXTVAL or CURRVAL of a sequence. • The default value datatype must match the column datatype.

  8. Table Instance Chart: DEPT Column name DEPTNO DNAME LOC Key type PK NN/UK NN, U NN FK table FK column Datatype NUMBER VARCHAR2 VARCHAR2 Length 2 14 13 Sample data 10 Finance 20 Sales 30 Sales 40 Sales

  9. Create a Table from a Table Instance Chart 1. Create a script file. Add CREATE TABLE table_name syntax. 2. Map the column names, datatypes, and lengths. 3. Map the NOT NULL constraints, except PRIMARY KEY, as column level constraints. 4. Map the PRIMARY KEY constraint. 5. Map the UNIQUE, CHECK, and FOREIGN KEY constraints. 6. Save and execute the script file.

  10. Datatypes Datatype VARCHAR2(size) CHAR(size) NUMBER NUMBER(p,s) DATE LONG RAW and LONG RAW Description Variable length character values Fixed length character values Floating point numbers Number values with precision and scale Date and time values Variable length character values up to 2 GB Variable length character for binary data

  11. Creating a Table • Create a table. • Confirm the table created using DESCRIBE command. CREATE TABLE dept2 (deptno number(2), dname varchar2(14), loc varchar2(13)); Table created.

  12. Creating a Table by Using a Subquery: Syntax • Create a table and insert rows by combining the CREATE TABLE command and AS subquery option. • Match number of specified columns to number of subquery columns. • Define columns with column names, and default values. CREATE TABLE table [column(, column...)] AS subquery;

  13. Creating a Table by Using a Subquery: Example • Create a table containing all employees in department number 10 in the EMP table. • Only the NOT NULL constraint will be copied. CREATE TABLE emp_10AS SELECT empno, ename, sal*12 ANNSAL, hiredateFROM empWHERE deptno = 10; Table created.

  14. Confirming Table Creation Confirm the existence of a database table along with its column names by using the SQL*Plus DESCRIBE command. SQL> DESCRIBE emp_10 Name Null? Type------------------------- -------- --------EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10)ANNSAL NUMBERHIREDATE DATE

  15. Constraints • Enforce rules at the table level. • Prevent the deletion of a table if there are dependencies. • The following constraint types are valid in Oracle: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK – specifies a condition that must be true.

  16. Constraint Guidelines • Name a constraint or the Server can generate a name by 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.

  17. Constraint: Syntax • Column-constraint level • Table-constraint level column [CONSTRAINT constraint_name] constraint_type, column,... [CONSTRAINT constraint_name] constraint_type (column, ...),

  18. Define Constraints: Example SQL> CREATE TABLE emp( empno number(4), ename varchar2(10), …… deptno number(7,2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO));

  19. The NOT NULL Constraint • Ensures that null values are not permitted for the column • Is defined at the column-constraint level Example CREATE TABLE friend (... phone VARCHAR2(15) NOT NULL,... last_name VARCHAR2(25) CONSTRAINT friend_last_name_nn NOT NULL,...);

  20. 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 or column-constraint level • Automatically creates a UNIQUE index ... phone VARCHAR2(10) CONSTRAINT emp_phone_uk UNIQUE,...

  21. 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 the table or column constraint level • Automatically creates a UNIQUE index ... id NUMBER(7) CONSTRAINT emp_id_pk PRIMARY KEY,...

  22. The FOREIGN KEY Constraint • 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 the table or column constraint level • Must match an existing value in the parent table or be NULL ... deptno NUMBER(2) CONSTRAINT emp_deptno_fk REFERENCES dept(deptno),...

  23. FOREIGN KEY Constraint Keywords • FOREIGN KEY • Defines the column in the child table at the table constraint level • REFERENCES • Identifies the table and column in the parent table • ON DELETE CASCADE • Allows deletion in the parent table and deletion of the dependent rows in the child table • ON DELETE SET NULL

  24. The CHECK Constraint • Defines a condition that each row must satisfy • Expressions that are not allowed: • References to pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM • Calls to SYSDATE, UID, USER, or USERENV functions • Queries that refer to other values in other rows • Is defined at either the table-constraint level or the column-constraint level ... deptno NUMBER(2) CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99),...

  25. Create Table: Example SQL> CREATE TABLE s_dept 2 (id NUMBER(7) 3 CONSTRAINT s_dept_id_pk PRIMARY KEY, 4 name VARCHAR2(25) 5 CONSTRAINT s_dept_name_nn NOT NULL, 6 region_id NUMBER(7) 7 CONSTRAINT s_dept_region_id_fk REFERENCES 8 s_region (id), 9 CONSTRAINT s_dept_name_region_id_uk UNIQUE 10 (name, region_id));

  26. Create Table: Example SQL> CREATE TABLE s_emp 2 (id NUMBER(7) 3 CONSTRAINT s_emp_id_pk PRIMARY KEY, 4 last_name VARCHAR2(25) 5 CONSTRAINT s_emp_last_name_nn NOT NULL, 6 first_name VARCHAR2(25), 7 userid VARCHAR2(8) 8 CONSTRAINT s_emp_userid_nn NOT NULL 9 CONSTRAINT s_emp_userid_uk UNIQUE, 10 start_date DATE DEFAULT SYSDATE, 11 comments VARCHAR2(25), 12 manager_id NUMBER(7), 13 title VARCHAR2(25), 14 dept_id NUMBER(7) 15 CONSTRAINT s_emp_dept_id_fk REFERENCES 16 s_dept (id), 17 salary NUMBER(11,2), 18 commission_pct NUMBER(4,2) 19 CONSTRAINT s_emp_commission_pct_ck CHECK 20 (commission_pct IN(10,12.5,15,17.5,20)));

  27. Summary • Build tables in a database by using the SQL CREATE TABLE command. • Construct a table with the following features: • Table name • Column names, datatypes, and lengths • Integrity constraints CREATE TABLE [schema.]table (column datatype [column_constraint], ... [table_constraint]);

  28. Summary • Constraint types • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK • UNIQUE indexes • Are created automatically with PRIMARY KEY and UNIQUE constraints. • SQL*Plus DESCRIBE command • Displays the structure of the table.

  29. Practice Overview • Creating new tables containing constraints • Verifying that the tables exist

  30. Practice 1 • Create DEPARTMENT table based on the following table instance chart. Enter the syntax in a script file and execute the script file to create the table. Confirm the table created. Column name ID NAME Key type PK NN/UK NN, U NN FK table FK column Datatype NUMBER VARCHAR2 Length 7 25

  31. Practice 2 • Populate the DEPARTMENT table with data from DEPT table. Include only columns that you need.

  32. Practice 3 • Create EMPLOYEE table based on the following table instance chart. Enter the syntax in a script file and execute the script file to create the table. Confirm the table created. Column name EID ENAME DEPTID Key type PK NN/UK NN, U NN FK table DEPARTMENT FK column ID Datatype NUMBER VARCHAR2 NUMBER Length 7 25 7

  33. Practice 4 • Populate the EMPLOYEE table with data from EMP table. Include only columns that you need.

More Related