Oracle SQL doubts

From: Kwok Ching Yuen <cykwok_at_singnet.com.sg>
Date: Sat, 28 Oct 2000 21:39:26 +0800
Message-ID: <8telac$vp8$1@mango.singnet.com.sg>

Hi, recently I'm trying some SQL stuffs on Oracle8i personal ED.

However, the following don't work:

1. The following statement won't work:

CREATE SCHEMA NADIA_DB AUTHORIZATION NADIA; 2. Tried to create domain in oracle, but encounter error. Maybe Oracle don't support domains.

CREATE DOMAIN PID_TYPE AS VARCHAR2(15)
/***************/

/* Oracle response */
/***************/

       *
ERROR at line 1:
ORA-00901: invalid CREATE command

Q1: Is it true that Oracle don't support the above features?

Q2: I tried the following, but it doesn't work. May I know how to implement the "ON UPDATE CASCADE" and "ON DELETE NO ACTION" features for the foreign key?

CREATE TABLE CUST(

 P_ID   VARCHAR2(15)  NOT NULL,
 S_LNAME  VARCHAR2(15)  NOT NULL,
 S_FNAME  VARCHAR2(15)  NOT NULL,
 S_BLOCK  VARCHAR2(5),
 S_STREET  VARCHAR2(20) NOT NULL,
 S_ZIP   VARCHAR2(10) NOT NULL,
 S_CITY   VARCHAR2(20),
 S_TEL   NUMBER(15) NOT NULL,
 R_LNAME  VARCHAR2(15)  NOT NULL,
 R_FNAME  VARCHAR2(15)  NOT NULL,
 R_BLOCK  VARCHAR2(5),
 R_STREET  VARCHAR2(20) NOT NULL,
 R_ZIP   VARCHAR2(10) NOT NULL,
 R_CITY   VARCHAR2(20),
 R_TEL   NUMBER(15) NOT NULL,

 PRIMARY KEY (P_ID),
 FOREIGN KEY (P_ID) REFERENCES PACK

   ON UPDATE NO ACTION        /* This won't work*/
   ON DELETE NO ACTION        /* This won't work*/

);

/***************/

/* Oracle response */
/***************/

   ON UPDATE NO ACTION
      *
ERROR at line 19:
ORA-00905: missing keyword

Q3. How do I create a table with the time data type? I get the following error.

CREATE TABLE FLIGHT(

 FLIGHT_NO VARCHAR2(10) NOT NULL,
 DEPART_TIME TIMESTAMP NOT NULL,
 ARRIVE_TIME TIMESTAMP NOT NULL,

 PRIMARY KEY (FLIGHT_N0)
);

/***************/

/* Oracle response */
/***************/

 DEPART_TIME TIMESTAMP NOT NULL,
             *
ERROR at line 3:
ORA-00902: invalid datatype

Q4. If I need to create a table (weak entity) whose primary key is also a foreign key which references another table (strong entity), how do I do that in Oracle SQL?

kumori. Received on Sat Oct 28 2000 - 08:39:26 CDT