1 / 63

Data Types Create Tables and Constraints

Data Types Create Tables and Constraints. Data Types Data types define acceptable values for constants, columns, host variables, functions and expressions. The ANSI Standard dictates a series of data types that all DBMS systems should provide Most DBMS systems implement these data types

curry
Download Presentation

Data Types Create Tables and Constraints

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. Data Types Create Tables and Constraints

  2. Data Types • Data types define acceptable values for constants, columns, host variables, functions and expressions. • The ANSI Standard dictates a series of data types that all DBMS systems should provide • Most DBMS systems implement these data types • The problem is in ‘how’ these types are implemented • A datatype on one DBMS may or may not work the same way on another DBMS

  3. Data Types • You will learn the basic data types offered by the Oracle DBMS system • These are similar data types to those used in the JAVA programming language • Note that there are other, more advanced data types available that will not be discussed • Also note that different DBMS systems may or may not have the same data types • All DBMS offer data types that work in a similar manner, they just may be named differently, and may operate slightly differently

  4. Character Data • A sequence of bytes. The length of the string is the number of bytes in the sequence. If the length is zero, the value is called the empty string. • CHAR(x) • Fixed-Length Character String • Can be defined to be between 1 and 2000 bytes • If the ‘(x)’ is omitted, a length of 1 is assumed • VARCHAR2(x) • Varying-Length Character String • Can contain from 0 to 4000 bytes • A value for ‘(x)’ must be provided • In the range 1 through 4000

  5. Numeric Data • All numbers in Oracle are defined using the ‘NUMBER(P,S)’ data type • Integers • Floating point numbers • Fixed point numbers • ‘P’ • Optional Precision refers to the total number of digits in the number • ‘S’ • Optional Scale refers to the number of digits after the decimal point • Different types of numbers are identified by using the precision and scale of the NUMBER data type

  6. Numeric Data (continued) • Integer: • Any numeric value that does not contain a decimal • A four byte integer has a precision of 10 digits • In Java, the value range is - 2147483648 up to 2147483647 • In Oracle, an Integer is defined ‘Number (10,0)’ or ‘Number(10)’ • The range is -9999999999 to 9999999999 • Any sized integer can be created by using a smaller or larger value for Precision • Number (5) • Range would be -99999 to 99999

  7. Numeric Data (continued) • Decimal • Any number where the number of decimal positions is fixed and does not change • Position of the decimal point is determined by precision (p) scale (s) • Precision (p) is the total number of digits and has to be less than 38 • Scale (s) is the number of digits in the fractional part and ranges from -87 to 127

  8. Numeric Data (continued) • Decimal • In Oracle, a decimal of 8 digits with 2 decimals would be defined • Number(8,2) • Valid range of values is -999999.99 through 999999.99 • The 8 refers to the total number of digits • The 2 means 2 of the 8 digits are the decimal portion

  9. Numeric Data (continued) • Decimal • In Oracle, a decimal of 8 digits with 2 decimals would be defined • Number(8,2) • If you attempt to place a number in this field that has more than 6 significant digits, Oracle will return an error. • IE If you attempt to place the number 1000000 in the field above, your statement will fail since 1000000 has 7 significant digits, the field can only hold 6

  10. Numeric Data (continued) • Decimal • In Oracle, a decimal of 8 digits with 2 decimals would be defined • Number(8,2) • If you insert a number with more that 2 decimals, Oracle will round the number to the second decimal place • i.e. if you place the value 56.7786 – Oracle will store 56.78 • i.e. If you place the value 32.7222 – Oracle will store 32.72

  11. Numeric Data (continued) • Double(): A decimal number, number of decimal positions can change • Defined by not providing a Position or Scale at all • Number

  12. DATETIME Values • Representations of dates and timestamps • Can be used in certain arithmetic and string operations and are compatible with certain strings, but, to the DBMS, they are neither strings nor numbers • Oracle DATETIME Datatypes • DATE: a four-part value (year, month, day, seconds since midnight) • TIMESTAMP: a seven-part value (year, month, day, hour, minute, second, and microsecond)

  13. Note: There are other Oracle datatypes not covered in this course

  14. Symbolic Constants • Special constants that return data values maintained by the DBMS • Examples: • CURRENT_DATE • Date and time today • CURRENT_TIMESTAMP • Date and time, as well as up to 6 digits partial seconds • Guaranteed to be unique - DBMS would never use the • same timestamp more than once • USER • Identifies the currently logged on user

  15. Creating Table • The CREATE TABLE command is used to create a table. • CREATE TABLE Students • ( • StNum NUMBER(10), • Name VARCHAR2(20), • Major VARCHAR2(20) • ); • Take note of the spacing in this and all the following slides • This alignment is not required, but eases reading • The above alignment is expected in all your work

  16. Removing a Table • The DROP TABLE command is used to remove a table from the database. • DROP TABLE Students; • The drop removes the table and its contents, so use it with care!

  17. Create Table is a command with many features. It support business rules, referential integrity, primary keys, NULL constraints, and so on. • First, we need to discuss the idea of constraints in the CREATE TABLE command.

  18. Constraints • Constraints are used in databases to enforce integrity rules. • An integrity constraint is a rule that restricts the values for one or more columns in a table. • The CONSTRAINT clause is used to specify constraints. • This clause can appear in either the CREATE TABLE or ALTERTABLE statements. • We now show how to define the major features of the CONSTRAINT clause when used within the CREATETABLE clause.

  19. Naming Integrity Constraints • When you create a constraint using the CONSTRAINT clause, you should provide a name for that constraint. • The name should reflect the nature of the constraint. • All constraints you create on your tables must have unique names • Primary key constraints are often called ‘entitynamePK’ • So, the primary key constraint name of the Students table might be ‘StudentsPK’

  20. Naming Integrity Constraints • If no name is supplied, a system generated name is assigned for you. • Usually quite cryptic, so make sure you specify a name! • Names allow constraints to be removed. • If you do not specify a name, you must search to find the name generated. • Typical unnamed constraint would look like this: SYS_C###### • The # refers to a numeric digit • These types of names are dangerous as cryptic names can lead to errors i.e. removing the wrong constraint

  21. Naming Integrity Constraints • Names help as they are displayed in an error message: • ERROR at line 1: • ORA-00001: unique constraint (STUDEN000.EMPLOYEEPK) violated • Without a name the same error may look this way: • ERROR at line 1: • ORA-00001: unique constraint (STUDEN000.SYS_C007985) violated

  22. The Major Types of Integrity Constraints • These are the types of constraints we shall deal with: • NULL* • PRIMARY KEY • UNIQUE • Referential (FOREIGN KEY) • CHECK • The general syntax for the CONSTRAINT clause (excluding NULL) is : • CONSTRAINT constraint_name constraint_type

  23. NULL Constraint • When a column is created for a table, it can have NULL values. • By setting the NOTNULL constraint for that column, the column cannot have the NULL value. • If a column does not have the ‘NOTNULL’ constraint, it isconsidered to be ‘Nullable’

  24. To do this we consider the following example : • CREATE TABLE Students • ( • StNum NUMBER(10) NOT NULL, • Name VARCHAR2(20), • Major VARCHAR2(20) • ); • The CONSTRAINT in the CREATETABLE clause is on the StNum attribute. • It specifies that StNum cannot be NULL while Name and Major may both be null. • Note that NOT NULL does not have a constraint name • A column can be made NULL/NOT NULL without referring to the constraint name

  25. To do this we consider the following example : • CREATE TABLE Students • ( • StNum NUMBER(10) NOT NULL, • Name VARCHAR2(20), • Major VARCHAR2(20) • ); • Note that there is no CONSTRAINT clause • This means the NOT NULL does not have a name • This is the only constraint type that does not need to be named • The NULL or NOT NULL status of a column can be changed by ALTERing the column without referring to the constraint name, so it is not really needed

  26. Non Key attributes can be nullable or not null, depending on user requirements • Attributes that must be provided should be set as ‘NOTNULL’ when the table is defined • Attributes that may or may not be provided should be set as • Nullable

  27. PRIMARY KEY CONSTRAINT • The primary key constraint designates that a column or a combination of columns is the table's primary key. • CREATE TABLE Students • ( • StNum NUMBER(10) NOT NULL, • Name VARCHAR2(20), • Major VARCHAR2(20), • CONSTRAINT StudentsPK • PRIMARY KEY (StNum) • ); • Notes: • The NOTNULL constraint is not required in Oracle – it is • assumed on any column that is part of a Primary Key • It should be use for clarity and to ensure the SQL is portable to • other DBMS systems • For this course, it is required

  28. A composite primary key is identified by specifying more than one column, separated by commas… • CREATE TABLE EMP • ( • EmpID NUMBER(10) NOT NULL, • Fname VARCHAR2(20) NOT NULL, • DeptID NUMBER(10) NOT NULL, • CONSTRAINT EmpIDPk • PRIMARY KEY(EmpID, DeptID) • );

  29. UNIQUE Constraint • Columns with the unique constraint cannot have duplicate values. • You usually assign a “NOTNULL” constraint to the unique column, but it is optional (in Oracle) • Note: Null values are not checked by the UNIQUE constraint, so it is possible two or more rows may exist where the unique column contains a null value • Remember, a PK must be unique, however a UNIQUE constraint does not need to be created on the PK since the PRIMARY KEY clauses enforces this automatically

  30. UNIQUE Constraint • Example One: • CREATE TABLE Students • ( • StNum NUMBER(10) NOT NULL, • Name VARCHAR2(20), • Major VARCHAR2(20), • SIN NUMBER(10) NOT NULL, • CONSTRAINT StudentsPK • PRIMARY KEY(StNum), • CONSTRAINT StuUnique • UNIQUE (SIN) • ); A ‘SIN’ number must be specified on every row ‘SIN’ number on each row must be different than all other rows. If not specified or it already exists, the insert or update will fail Note: Unique constraints are rare as columns other than PK columns usually don’t need to be unique

  31. UNIQUE Constraint • Example Two: • CREATE TABLE Students • ( • StNum NUMBER(10) NOT NULL, • Name VARCHAR2(20), • Major VARCHAR2(20), • SIN NUMBER(10), • CONSTRAINT StudentsPK • PRIMARY KEY(StNum), • CONSTRAINT StuUnique • UNIQUE (SIN) • ); A ‘SIN’ number is optional on a row If specified (not null) it must be different than all other rows Two or more rows may have a NULL ‘SIN’ number

  32. Composite UNIQUE Constraint • You can place the UNIQUE constraint across several columns. • This is called a composite unique constraint. • A composite unique constraint requires that the combination of the unique key column is unique and that the combination does not repeat. • All unique columns may also be null. • Warning: Null composite unique constraints work in an unusual way!!!

  33. Composite UNIQUE Constraint Example 1 – Columns Not Null: CREATE TABLE EMP ( EmpID NUMBER(10) NOT NULL, Fname VARCHAR2(20) NOT NULL, Lname VARCHAR2(20) NOT NULL, DeptID NUMBER(10) , CONSTRAINT EmpUnique UNIQUE (Fname, Lname) ); I can have as many first name ‘David’ as I like, provided all the last names differ… I can have as many last name ‘Jones’ as I like, provided all first names differ… I can only nave one row with a First Name of ‘David’ and a Last Name of ‘Jones’

  34. Composite UNIQUE Constraint Example 2 Columns Nullable: CREATE TABLE EMP ( EmpID NUMBER(10) NOT NULL, Fname VARCHAR2(20), Lname VARCHAR2(20), DeptID NUMBER(10), CONSTRAINT EmpUnique UNIQUE (Fname, Lname) ); Same restrictions as before, plus: I can have multiple rows where the FName and LName are both NULL Here is where it gets weird…

  35. Composite UNIQUE Constraint Example 2 Columns Nullable: CREATE TABLE EMP ( EmpID NUMBER(10) NOT NULL, Fname VARCHAR2(20), Lname VARCHAR2(20), DeptID NUMBER(10), CONSTRAINT EmpUnique UNIQUE (Fname, Lname) ); I can have only one first name (‘David’) where the last name is Null… I can have only one last name (‘Jones’) where the first name is Null…

  36. Composite UNIQUE Constraint EMPID FNAME LNAME ------ ---------- ---------- 1 David Jones 1 David Smith 1 Eileen Jones 1 David Only one ‘David’ Null is allowed 1 Jones Only one Null ‘Jones’ is allowed 1 1 Multiple Null Null allowed 1

  37. Referential Integrity constraint • or Foreign Keys • Referential integrity constraints are used to enforce the business rules that govern the relationship between columns of different tables. • These constraints allows you to develop a parent-child relationship between tables. • The parent table contains the reference (primary) key and the child table contains the foreign key. • You specify the integrity constraint(s) in the child table.

  38. Example: • CREATE TABLE Students • ( • StNum NUMBER(10) NOT NULL, • Name VARCHAR2(20), • Major VARCHAR2(20), • SIN NUMBER(10), • ProgID NUMBER(10), • CONSTRAINT StudentsPK • PRIMARY KEY(StNum), • CONSTRAINT StuUnique • UNIQUE (SIN), • CONSTRAINT EnrollFK • FOREIGN KEY(ProgID) • REFERENCES Programs • ); • Does this work? • No! – The programs table must be created first

  39. Example: CREATE TABLE Programs ( ProgID NUMBER(10) NOT NULL ); Then create the Students table… How about now? No! – The parent table requires the Primary Key be defined

  40. Example: CREATE TABLE Programs ( ProgID NUMBER(10) NOT NULL, CONSTRAINT ProgramsPK PRIMARY KEY (ProgID) ); Then create the Students table. It should be Successful.

  41. Constraint EnrollFK FOREIGN KEY (ProgID) REFERENCES Programs • The foreign key ensures that all students in the table are enrolled in a program from the Programs table. • If a statement attempts to change an students program (ProgID) to a value that is not in the primary key of the Programs table, the statement will fail. • The foreign key may be null as well, depending on how the relationship is defined in the ERD

  42. The name of the foreign key column in the child may be the same or different from the primary key in the parent • The Foreign Key clause specifies the name of the parent table only, not the name of the primary key column(s) in the parent • Before a Foreign Key can be created, the parent table must be created and its primary key defined

  43. What happens if we try to delete a row from the parent table, but there are children of that parent? • The delete will fail • We cannot delete a parent with children, otherwise we will create 'Orphans'

  44. Cascading Delete of Foreign Key • In order to keep a database consistent, deleting a primary key in the parent table may affect the child table. • Rows (dependent rows) in the child table might need to be deleted whenever a row in the parent table is deleted. • This is because the row from the parent table is referenced by rows from the child table. • In order to delete all rows in a child table that is associated with a row that is to be deleted in the parent table, we use the ONDELETECASCADE option.

  45. CREATE TABLE Programs ( • ProgID NUMBER(10) NOT NULL, • CONSTRAINT ProgramsPK • PRIMARY KEY (ProgID)); • CREATE TABLE Students ( • StNum NUMBER(10) NOT NULL, • Name VARCHAR2(20), • Major VARCHAR2(20), • SIN NUMBER(10), • ProgID NUMBER(10), • CONSTRAINT StudentsPK • PRIMARY KEY(StNum), • CONSTRAINT StuUnique • UNIQUE (SIN), • CONSTRAINT EnrollFK • FOREIGN KEY(ProgID) • REFERENCES Programs • ON DELETE CASCADE • );

  46. Because of the ONDELETECASCADE option, the DBMS cascades any deletion of a ProgID value in the Programs table to the ProgID values of its dependent rows of the Students Table. • For example, if Progam 10 (BIT) is deleted from the Programs table, the DBMS deletes all rows in the Students table where the ProgID value is 10. • Deletes cascade – So if Student is a parent, the delete in Programs may result in rows being deleted in the children of the Students table • Cascade can delete a large amount of data with a single statement!

  47. Opposite to “ONDELETECASCADE” is the ‘restrict’ option • Example: • If department 10 is deleted from the Dept table, the DBMS checks the EMP table. If any employees work in department 10, the delete of the department will fail if ‘restrict’ is in place • If you want to prevent a parent row from being deleted when it has children, use the restrict option • ‘Restrict’ is the default action provided by Oracle • To implement ‘restrict’ omit the ‘On Delete’ clause • Note: Placing ‘ON DELETE RESTRICT’ will cause an error in Oracle

  48. CREATE TABLE Programs ( • ProgID NUMBER(10) NOT NULL, • CONSTRAINT ProgramsPK • PRIMARY KEY (ProgID)); • CREATE TABLE Students ( • StNum NUMBER(10) NOT NULL, • Name VARCHAR2(20), • Major VARCHAR2(20), • SIN NUMBER(10), • ProgID NUMBER(10), • CONSTRAINT StudentsPK • PRIMARY KEY(StNum), • CONSTRAINT StuUnique • UNIQUE (SIN), • CONSTRAINT EnrollFK • FOREIGN KEY(ProgID) • REFERENCES Programs • ); • Note: No ‘ON DELETE’ clause means ‘RESTRICT’

  49. A middle of the row approach is to use ‘ON DELETE SET NULL’ • This will allow the parent key to be deleted even if it has children • Any children have their foreign key set to Null • This in effect removes ‘Orphans’ • For this reason, columns that are a foreign key are often defined as nullable • The ‘NOTNULL’ keyword is not used on the foreign key columns when the table is created • If the Foreign Key is set as NOT NULL, then 'ON DELETE SET NULL' cannot be used

  50. CREATE TABLE Programs ( • ProgID NUMBER(10) NOT NULL, • CONSTRAINT ProgramsPK • PRIMARY KEY (ProgID)); • CREATE TABLE Students ( • StNum NUMBER(10) NOT NULL, • Name VARCHAR2(20), • Major VARCHAR2(20), • SIN NUMBER(10), • ProgID NUMBER(10), • CONSTRAINT StudentsPK • PRIMARY KEY(StNum), • CONSTRAINT StuUnique • UNIQUE (SIN), • CONSTRAINT EnrollFK • FOREIGN KEY(ProgID) • REFERENCES Programs • ON DELETE SET NULL • );

More Related