Re: ORA-02253

OOPS! Remove foot from mouth. I've gotten in the habit of creating indexes for most foreign keys and I bundle them together at the end of the create statement. Should have said you can't do what you were trying to do.

"Van Messner" <vmessner_at_bestweb.net> wrote in message news:gKPx5.245$mC.8180_at_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:50:29 CDT