1 / 25

SQL Data Definition

SQL Data Definition. using Oracle. DDL. SQL = DDL + DML Oracle has its own dialect of SQL DDL (Data definition language) Part of SQL Some DDL statements create table … Fig. 4-4, page 77 alter table … drop table …. Naming conventions, 68.

tobias
Télécharger la présentation

SQL Data Definition

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. SQL Data Definition using Oracle SQL data definition using Oracle

  2. DDL • SQL = DDL + DML • Oracle has its own dialect of SQL • DDL (Data definition language) • Part of SQL • Some DDL statements • create table … • Fig. 4-4, page 77 • alter table … • drop table … SQL data definition using Oracle

  3. Naming conventions, 68 • Naming conventions for table names and attributes names • Illegal • spaces • hyphens • Legal • letters [a-z A-Z, not æøå] + digits • _, #, $ • first character must be a letter • no reserved words in SQL [no attribute called 'by‘ or ‘table’] • Fig. 4-1, page 68 SQL data definition using Oracle

  4. Data types, 68 • Slightly different from standard SQL • Strings • Numbers • Dates • Other • no Boolean data type SQL data definition using Oracle

  5. Data types, strings, 69 • varchar2 (max_length) • varying length, like names, etc. • Varchar2(10) • Max length 10 bytes • Varchar2(10 char) • Max length 10 chars • Normally 1 char ~ 1 byte, but it depends on the character table (like UNICODE) • char (fixed_length) • fixed length, like cpr, phone, etc • Length in bytes or chars, like varchar2 SQL data definition using Oracle

  6. Data types, numbers, 70 • Numbers • number (digits, decimals) • fixed number of digits and decimals, like price, salary, etc. • number (digits) integer • number floating point (high precision) SQL data definition using Oracle

  7. Data types, dates, 70 • date • store date and time! • no special time data type • Oracle SQL has special functions to do arithmetic on dates • more on that when we look at "select …" • advice • don't make an attribute called "age", as it changes frequently. • make an attribute called birthday. SQL data definition using Oracle

  8. Data types, other, 72 • CLOB character large object • texts up to 4 gigabytes • BLOB binary large object • up to 4 gigabytes, pictures, sounds, etc. • NCLOB • like CLOB, but using 2 bytes pr. character • BFILE binary file • reference to a binary file outside the DBMS • Summary, fig. 4-2, page 72 SQL data definition using Oracle

  9. Constraints, types, 72 • Types of constraints • integrity constraints • primary key • foreign key • value constraints • not null • unique SQL data definition using Oracle

  10. Constraints, naming, 72 • A constraints must have a name to be able to • alter the constraints • delete the constraint • naming convention • tableName_attributeName_constraintType • student_cpr_pk • fig. 4-3, page 73 • If you don’t name your constraints Oracle will name them. Find the names • select * from user_constraints where table_name = 'PERSON'; SQL data definition using Oracle

  11. Constraints, defining + primary key, 73 • 2 ways to define a constraint • column level, applied to one attribute create table student ( cpr char (9) constraint student_cpr_pk primary key, … ) • table level, can be applied to more attributes create table student_course ( cpr char(9) not null, course_number number(3) not null, constraint student_cpr_pk primary key (cpr, course_number) ) SQL data definition using Oracle

  12. Constraints, foreign key, 74 • Attributes in one table refers to attributes in [another] table. • DBMS checks if the referred attributes exist. • referential integrity • Example create table student_course ( cpr char(9) not null, course_number number(3) not null constraint student_course_number_fk references course (course_number), constraint student_cpr_pk primary key (cpr, course_number), constraint student_cpr_fk foreign key (cpr) references student (cpr) ); SQL data definition using Oracle

  13. Referential triggered actions, (not in the book) • foreign key … on delete cascade • if the referred row is deleted the referring row should be deleted as well create table employee ( cpr char(9) primary key, … boss_cpr char(9), foreign key (boss_cpr) references employee (cpr) on delete cascade) ) • If we delete the top boss the whole organization vanishes!! • think carefully before writing … on delete cascade • useful in weak entity sets • no … on update cascade in Oracle!! • not necessary if we use surrogate keys (they never change) SQL data definition using Oracle

  14. Constraints, not null, 75 • The attribute must no be NULL. • syntaxes • name varchar2(30) not null no name constraint • name varchar2(30) constraint student_name_nnnot null • can not be specified at table level [only at attribute level] • primary key attributes must be NOT NULL • NULL means • does not exist • is not known at the moment • Don't make your own NULL's using 0 or ‘ ‘ • Don’t write NOT NULL to often. • Think before you write! SQL data definition using Oracle

  15. Constraints, unique, 75 • The value of an attribute must be unique • used when a table has more candidate keys • one of the candidates is appointed primary key • the rest of the candidates are [only] unique • syntaxes • attribute level • departmentName varchar(12) unique • table level • constraint student_name_uk unique (firstname, lastname) unlikely constraint!! SQL data definition using Oracle

  16. Constraint, check, 75 • Checking business rules • General constraint that every row must satisfy • attribute level • salary number(5) check ( salary >= 0 ) • gender char(1) check (gender in ('M', 'F')) • table level • check (gender ='M' or salary < '1000') SQL data definition using Oracle

  17. Default value + domains, 76 • No a constraint, but has similar syntax. • Used when no value is supplied • country char(2) default 'DK' • salary number(5) default 0 • Oracle has no domain concept • No create domain … • But there is a create type … SQL data definition using Oracle

  18. Creating a table, 76 • General syntax in Oracle • Page 76 • Examples • Fig. 4-4, page 77 • Tables are usually created by a DBA (Database administrator) • Tables are usually not created from an application (Java program) SQL data definition using Oracle

  19. Displaying table information • Forgot the name of your tables? • Select table_name from user_tables • User_tables has information in all your tables • Forgot the name + type of attributes? • Describe Person SQL data definition using Oracle

  20. Using an SQL editor • Use an moderns SQL editor • Work with your SQL statements until they are correct. • Copy / past the SQL statement to a text file - and save it. • It's handy to have the SQL statements for documentation + maintenance. SQL data definition using Oracle

  21. Alter table, 82 • Altering an existing table • add / delete attributes • add / delete constraints • most alternations are restricted [hard to do] so design your tables carefully before creating them!! • syntax • alter table tableNametheAlternation • alter table student add mobilPhone char(8) SQL data definition using Oracle

  22. Chicken and egg problem • From http://www-db.stanford.edu/~ullman/fcdb/oracle/or-triggers.html • Problem • If table A refers to table B and vice versa which table should be created first? • Solution • create table A without foreign key to B • create table B with foreign key to A • alter table A adding a foreign key to B SQL data definition using Oracle

  23. Chicken and egg, example create table chicken ( cID number(3) primary key, eID number(3) ) ------------------------- create table egg ( eID number (3) primary key, cID number (3), foreign key (cID) refers to chicken (cID) initially deferred deferrable ) ------------------------ alter table chicken add constraint chicken_eID_fk foreign key (eID) refers to egg (eID) initially deferred deferrable SQL data definition using Oracle

  24. Chicken and egg, insertion • How to insert rows into the tables? • Problem • an egg row refers to a chicken row, and vice versa • Solution (Oracle) • create the foreign keys with "initially deferred deferrable" • defers constraint checking until transaction commit • insert into chicken (1,2) • insert into egg (2,1) • commit SQL data definition using Oracle

  25. Drop tables, etc. 89 • truncate table tableName • remove all data from the table, but the [not empty] table still exists. • use carefully!! • drop table • deletes the table including data • use carefully!! • Referential integrity • You are not allowed to drop a table if another table refer to it • rename oldTableName to newTableName SQL data definition using Oracle

More Related