Re: ORA-02253

From: Van Messner <vmessner_at_bestweb.net>
Date: Tue, 19 Sep 2000 20:09:48 GMT
Message-ID: <gKPx5.245$mC.8180@monger.newsread.com>

Its what the rrror says - you can't put the constraint clause there. Here's one example -

CREATE TABLE addresses(

 address_id                          NUMBER(8)                  NOT NULL,
 postal_code                         INTEGER                    NOT NULL,
 street_number                    VARCHAR2(10)               NULL,
 street_name                        VARCHAR2(35)               NOT NULL,
 address_line_2                     VARCHAR2(35)               NULL,
 address_line_3                  VARCHAR2(35)               NULL,
 directions                      VARCHAR2(2000)             NULL,
 geo_code                        VARCHAR2(10)               NULL

)
PCTFREE 20
PCTUSED 80
TABLESPACE CONT
STORAGE(INITIAL 256 NEXT 256 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 249); COMMENT ON COLUMN addresses.address_id

    IS 'System generated unique id for this address'; COMMENT ON COLUMN addresses.postal_code

    IS 'The ZIP or postal code for the address'; COMMENT ON COLUMN addresses.street_number

    IS 'The street number of the address'; COMMENT ON COLUMN addresses.street_name

    IS 'The street name line of the address'; COMMENT ON COLUMN addresses.address_line_2

    IS 'The second line of the address'; COMMENT ON COLUMN addresses.address_line_3

    IS 'The third line of the address';
COMMENT ON COLUMN addresses.directions

    IS 'Step by step instuctions on how to arrive at the address'; COMMENT ON COLUMN addresses.geo_code

    IS 'Unique alphanumeric identifier for this geographic area.'; PROMPT
PROMPT Creating Index ADDR_GEO_FK_I on Table ADDRESSES CREATE INDEX ADDR_GEO_FK_I ON ADDRESSES
  (GEO_CODE)
  TABLESPACE CONT_INDEX
    STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 249)
  PCTFREE 40
;
PROMPT Adding PRIMARY Constraint To ADDRESSES Table ALTER TABLE ADDRESSES ADD (
  CONSTRAINT ADDR_PK
  PRIMARY KEY (ADDRESS_ID)
  USING INDEX TABLESPACE CONT_INDEX
    STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 249)
  PCTFREE 10
);
PROMPT Adding FOREIGN Constraint To ADDRESSES Table ALTER TABLE ADDRESSES ADD (
  CONSTRAINT ADDR_GEO_FK
  FOREIGN KEY (GEO_CODE)
  REFERENCES GEOGRAPHIC_BOUNDARIES (GEO_CODE) );

"Rene Nyffenegger" <renenyffenegger_at_my-deja.com> wrote in message news:8q7b7i$uu7$1_at_nnrp1.deja.com...
> Hi
> Trying to create two tables:
>
> CREATE TABLE PR_NUMBER (
> PR_NUMBER_ID NUMBER CONSTRAINT PK_PR_NUMBER PRIMARY KEY,
> RAW_PR_NUMBER CHAR ( 6),
> HS CHAR ( 2),
> VALID_FROM DATE NOT NULL,
> MODEL_CODE VARCHAR2( 10),
> PROCESSING_CODE NUMBER,
> CONSTRAINT UQ_PR_NUMBER UNIQUE (RAW_PR_NUMBER, VALID_FROM,
> MODEL_CODE)
> )
> TABLESPACE USERS;
>
> CREATE PUBLIC SYNONYM PR_NUMBER FOR PR_NUMBER;
>
> CREATE TABLE TEXT_PR_NUMBER (
> ID CONSTRAINT FK_PN_TEXT_PN REFERNECES PR_NUMBER
> (PR_NUMBER_ID),
> TEXT VARCHAR2(999)
> )
> TABLESPACE USERS;
>
> CREATE PUBLIC SYNONYM TEXT_PR_NUMBER FOR TEXT_PR_NUMBER;
>
> I get an ORA-02253 at the 2nd table (text_pr_number) for the
> column ID.
>
> Why is that?
>
> Rene
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Sep 19 2000 - 15:09:48 CDT