Results 1 to 11 of 11

Thread: Sqlplus Newb

  1. #1
    Join Date
    May 2008
    Posts
    97

    Unanswered: Sqlplus Newb

    Hello, I'm new to Database Administration and I'm following a bunch of tutorials online to learn about this field. I'm a just a novice with this so please bare with me.

    This scripts starts with a series of DROP statements so that as you correct mistakes you will start fresh each time. To avoid referential integrity errors, the drops should be in the opposite order of the create table statements.
    What on earth is 'referential integrity errors'??

    the table is below

    Code:
    drop table EMPLOYEE cascade constraints;
    drop table PROSPECT cascade constraints;
    drop table SERVWORK cascade constraints;
    drop table SERVINV cascade constraints;
    drop table INVOPTION cascade constraints;
    drop table BASEOPTION cascade constraints;
    drop table OPTIONS cascade constraints;
    drop table SALEINV cascade constraints;
    drop table CAR cascade constraints;
    drop table CUSTOMER cascade constraints;
    
    CREATE TABLE CUSTOMER
    (CNAME    CHAR(20) PRIMARY KEY,
     CSTREET  CHAR(20) NOT NULL,
     CCITY    CHAR(20) NOT NULL,
     CPROV    CHAR(20) NOT NULL,
     CPOSTAL  CHAR(10),
     CHPHONE  CHAR(13),
     CBPHONE  CHAR(13));
    
    CREATE TABLE CAR
    (SERIAL       CHAR(8) PRIMARY KEY,
     CNAME        CHAR(20) REFERENCES CUSTOMER(CNAME),
     MAKE         CHAR(10) NOT NULL,
     MODEL        CHAR(8) NOT NULL,
     CYEAR        CHAR(4) NOT NULL,
     COLOR        CHAR(12) NOT NULL,
     TRIM         CHAR(16) NOT NULL,
     ENGINETYPE   CHAR(10) NOT NULL,
     PURCHINV     CHAR(6),
     PURCHDATE    DATE,
     PURCHFROM    CHAR(12),
     PURCHCOST    NUMBER(9,2),
     FREIGHTCOST  NUMBER(7,2),
     TOTALCOST    NUMBER(9,2),
     LISTPRICE    NUMBER(9,2));
    
    
    CREATE TABLE SALEINV
    (SALEINV      CHAR(6) PRIMARY KEY,
     CNAME        CHAR(20) NOT NULL REFERENCES CUSTOMER (CNAME),
     SALESMAN     CHAR(20),
     SALEDATE     DATE NOT NULL CHECK (SALEDATE > 
                  TO_DATE('01-JAN-1990','DD-MON-YYYY')),
     SERIAL       CHAR(8)  NOT NULL REFERENCES CAR (SERIAL),
     TOTALPRICE   NUMBER(9,2),
     DISCOUNT     NUMBER(8,2),
     NET          NUMBER(9,2),
     TAX          NUMBER(8,2),
     LICFEE       NUMBER(6,2),
     COMMISSION   NUMBER(8,2),
     TRADESERIAL  CHAR(8) REFERENCES CAR (SERIAL),
     TRADEALLOW   NUMBER(9,2),
     FIRE         CHAR(1) CHECK (FIRE IN ('Y','N')),
     COLLISION    CHAR(1) CHECK (COLLISION IN ('Y','N')),
     LIABILITY    CHAR(1) CHECK (LIABILITY IN ('Y','N')),
     PROPERTY     CHAR(1) CHECK (PROPERTY IN ('Y','N')));
    
    
    CREATE TABLE OPTIONS
    (OCODE CHAR(4) PRIMARY KEY,
     ODESC CHAR(30),
     OCOST NUMBER(7,2),
     OLIST NUMBER(7,2));
    
    
    CREATE TABLE BASEOPTION
    (SERIAL  CHAR(8) REFERENCES CAR(SERIAL),
     OCODE   CHAR(4) REFERENCES OPTIONS(OCODE),
     PRIMARY KEY (SERIAL,OCODE));
    
    
    
    CREATE TABLE INVOPTION
    (SALEINV   CHAR(6) NOT NULL REFERENCES SALEINV (SALEINV),
     OCODE     CHAR(4) NOT NULL REFERENCES OPTIONS (OCODE),
     SALEPRICE NUMBER(7,2) NOT NULL,
     UNIQUE (SALEINV, OCODE));
    
    
    
    CREATE TABLE SERVINV
    (SERVINV   CHAR(5) PRIMARY KEY,
     SERDATE   DATE NOT NULL,
     CNAME     CHAR(20) NOT NULL REFERENCES CUSTOMER(CNAME),
     SERIAL    CHAR(8)  NOT NULL REFERENCES CAR(SERIAL),
     PARTSCOST NUMBER(7,2),
     LABORCOST NUMBER(7,2),
     TAX       NUMBER(6,2),
     TOTALCOST NUMBER(8,2));
    
    
    
    CREATE TABLE SERVWORK
    (SERVINV  CHAR(5) REFERENCES SERVINV(SERVINV),
     WORKDESC CHAR(80),
     CONSTRAINT PKSW PRIMARY KEY (SERVINV,WORKDESC));
    
    
    
    CREATE TABLE PROSPECT
    (CNAME CHAR(20) NOT NULL REFERENCES CUSTOMER(CNAME),
     MAKE  CHAR(10) NOT NULL CHECK (MAKE IN ('ACURA','MERCEDES','LAND ROVER','JAGUAR')),
     MODEL CHAR(8),
     CYEAR CHAR(4),
     COLOR CHAR(12),
     TRIM  CHAR(16),
     OCODE CHAR(4) REFERENCES OPTIONS(OCODE),
     UNIQUE (CNAME,MAKE,MODEL,CYEAR,COLOR,TRIM,OCODE));
    
    
    
    CREATE TABLE EMPLOYEE
    (EMPNAME CHAR(20) PRIMARY KEY,
     STARTDATE DATE NOT NULL,
     MANAGER CHAR(20) REFERENCES EMPLOYEE(EMPNAME),
     COMMISSIONRATE NUMBER(2),
     TITLE CHAR(26));
    Last edited by Eric the Red; 05-30-08 at 12:29. Reason: erratum

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am sorry to see that GOOGLE is broken for you.
    Results 1 - 10 of about 82,000 for oracle 'referential integrity errors'?
    Please be patient while repairs are being completed.
    More complete response will soon follow.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Certainly

    It means that you can't drop a "parent/master" table while its "child/detail" table exists. So, first drop details, then masters. Here's a simple example - two tables, one references another (employees and departments - every employee works in one of departments, so there's referential integrity constraint present which will not allow you to insert an employee who works in non-existent department).

    First, working environment:
    Code:
    SQL> create table dept (deptno number primary key,
      2                     dname  varchar2(20));
    
    Table created.
    
    SQL> create table emp (empno   number primary key,
      2                    ename   varchar2(20),
      3                    deptno  number constraint fk_ed
      4                                   references dept (deptno));
    
    Table created.
    
    SQL> insert all
      2    into dept (deptno, dname) values (100, 'Iceberg')
      3    into emp  (empno, ename, deptno) values (1, 'Little', 100)
      4  select * from dual;
    
    2 rows created.
    
    SQL> commit;
    
    Commit complete.
    Now, deleting data from these tables:
    Code:
    SQL> -- Deleting parent table (dept) first - it won't work:
    SQL> delete from dept;
    delete from dept
    *
    ERROR at line 1:
    ORA-02292: integrity constraint (MIKE.FK_ED) violated - child record found
    
    
    SQL> -- Deleting child table (emp) - it will be OK:
    SQL> delete from emp;
    
    1 row deleted.
    
    SQL> -- Now delete parent as well - it will be OK as well:
    SQL> delete from dept;
    
    1 row deleted.
    
    SQL>
    I hope it is a little bit clearer now what "referential integrity error" means.

    P.S. "DROP" will produce the same result as "DELETE"; you may try it.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Littlefoot
    P.S. "DROP" will produce the same result as "DELETE"; you may try it.
    No it will not!

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    True, it will not. I apologize.

    Error will be different, but the final result will be the same - you can't drop master when its deteils exist:
    Code:
    SQL> -- Dropping master table isn't possible when there are detail records:
    SQL> drop table dept;
    drop table dept
               *
    ERROR at line 1:
    ORA-02449: unique/primary keys in table referenced by foreign keys
    
    
    SQL> -- First drop detail ...
    SQL> drop table emp;
    
    Table dropped.
    
    SQL> -- ... then drop master:
    SQL> drop table dept;
    
    Table dropped.
    
    SQL>

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    While we're at it, another point: referential integrity constraint may be defined as deferrable. It means that there'll be no checking untill the COMMIT point. Here's an example:
    Code:
    SQL> CREATE TABLE dept (deptno NUMBER PRIMARY KEY,
      2                     dname  VARCHAR2(20));
    
    Table created.
    
    SQL> CREATE TABLE emp (empno   NUMBER PRIMARY KEY,
      2                    ename   VARCHAR2(20),
      3                    deptno  NUMBER CONSTRAINT fk_ed
      4                                   REFERENCES dept (deptno)
      5                                   INITIALLY DEFERRED DEFERRABLE);
    
    Table created.
    
    SQL> -- Now, we'll insert details first; deferrable constraint will allow it:
    SQL> insert into emp (empno, ename, deptno) values (1, 'Little', 100);
    
    1 row created.
    
    SQL> -- See? Although there are no records in the 'dept' table, everything's
    SQL> -- fine - so far. If we try to commit the transaction - it will fail:
    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-02091: transaction rolled back
    ORA-02291: integrity constraint (MIKE.FK_ED) violated - parent key not found
    
    
    SQL> select * from emp;
    
    no rows selected
    
    SQL> insert into emp (empno, ename, deptno) values (1, 'Little', 100);
    
    1 row created.
    
    SQL> insert into dept (deptno, dname) values (100, 'Iceberg');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL>
    Code:
    SQL> -- The same goes for deleting: we can delete master table first:
    SQL> delete from dept;
    
    1 row deleted.
    
    SQL> -- But, we can't commit it:
    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-02091: transaction rolled back
    ORA-02292: integrity constraint (MIKE.FK_ED) violated - child record found
    
    
    SQL> -- OK, delete details first:
    SQL> delete from emp;
    
    1 row deleted.
    
    SQL> delete from dept;
    
    1 row deleted.
    
    SQL> commit;
    
    Commit complete.
    
    SQL>
    Code:
    SQL> -- Dropping tables: no difference from the previous dropping - details
    SQL> -- first, master second:
    SQL> drop table dept;
    drop table dept
               *
    ERROR at line 1:
    ORA-02449: unique/primary keys in table referenced by foreign keys
    
    
    SQL> drop table emp;
    
    Table dropped.
    
    SQL> drop table dept;
    
    Table dropped.
    
    SQL>

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You are aware that you can use "CASCADE CONSTRAINT" to also drop the FK constraints when dropping a table?
    Code:
    drop table dept cascade constraints;
    drop table emp;

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Who, me? What do you think?

    I'll make it easier for you ... yes, I am aware. Now. I wasn't even THINKING about it earlier today. Shame on me, I'm really embarrassed.

  9. #9
    Join Date
    May 2008
    Posts
    97
    I ran the script I posted above with 'cascade constraints' removed from the drop statements and I'm getting the following errors.. now why is this so complicated. Why am I getting all these errors.. this is completely overwhelming.

    Notably I got, 'ORA-01658: unable to create INITIAL extent for segment in tablespace USERS' so... to fix it I tried 'ALTER DATABASE DATAFILE <CreateTempTables> RESIZE 200Mb' but that doesn't work either.

    Wow, any help would be 100% appreciated!

    Code:
    SQL> @CreateTempTables
    CREATE TABLE CUSTOMER
                 *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object
    
    
    CREATE TABLE CAR
    *
    ERROR at line 1:
    ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
    
    
     SERIAL       CHAR(8)  NOT NULL REFERENCES CAR (SERIAL),
                                               *
    ERROR at line 7:
    ORA-00942: table or view does not exist
    
    
    CREATE TABLE OPTIONS
    *
    ERROR at line 1:
    ORA-01658: unable to create INITIAL extent for segment in tablespace USERS
    
    
    (SERIAL  CHAR(8) REFERENCES CAR(SERIAL),
                                *
    ERROR at line 2:
    ORA-00942: table or view does not exist
    
    
    (SALEINV   CHAR(6) NOT NULL REFERENCES SALEINV (SALEINV),
                                           *
    ERROR at line 2:
    ORA-00942: table or view does not exist
    
    
     SERIAL    CHAR(8)  NOT NULL REFERENCES CAR(SERIAL),
                                            *
    ERROR at line 5:
    ORA-00942: table or view does not exist
    
    
    (SERVINV  CHAR(5) REFERENCES SERVINV(SERVINV),
                                 *
    ERROR at line 2:
    ORA-00942: table or view does not exist
    
    
     OCODE CHAR(4) REFERENCES OPTIONS(OCODE),
                              *
    ERROR at line 8:
    ORA-00942: table or view does not exist
    
    
    CREATE TABLE EMPLOYEE
    *
    ERROR at line 1:
    ORA-01658: unable to create INITIAL extent for segment in tablespace USERS

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    drop table EMPLOYEE cascade constraints;
    drop table PROSPECT cascade constraints;
    drop table SERVWORK cascade constraints;
    drop table SERVINV cascade constraints;
    drop table INVOPTION cascade constraints;
    drop table BASEOPTION cascade constraints;
    drop table OPTIONS cascade constraints;
    drop table SALEINV cascade constraints;
    drop table CAR cascade constraints;
    drop table CUSTOMER cascade constraints;
    You are supposed to do the above before doing the CREATE commands
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    May 2008
    Posts
    97
    Quote Originally Posted by anacedent
    Code:
    drop table EMPLOYEE cascade constraints;
    drop table PROSPECT cascade constraints;
    drop table SERVWORK cascade constraints;
    drop table SERVINV cascade constraints;
    drop table INVOPTION cascade constraints;
    drop table BASEOPTION cascade constraints;
    drop table OPTIONS cascade constraints;
    drop table SALEINV cascade constraints;
    drop table CAR cascade constraints;
    drop table CUSTOMER cascade constraints;
    You are supposed to do the above before doing the CREATE commands
    No these drop statements delete the tables before they're re-created.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •