ORA-02253: constraint specification not allowed here

Question

ORA-02253: constraint specification not allowed here

Navigation

#1 by (0 votes)

1

Hello, I have this table created in oracle :

CREATE TABLE SUBCONTRACT(
CIF CHAR(9) NOT NULL CONSTRAINT PK_SUBCONTRACTS_CIF primary key,
NAME VARCHAR2(50) NOT NULL,
ADDRESSSTREET varchar2(50) not null,
ADDRESSNUM  number(7,0) not null,
ADDRESSFLOOR varchar2(4) null,
ACCOUNTNUMBER char(24) not null,
EMAIL varchar2(50) not null,
PRICES NUMBER(10,2),
SERVICE VARCHAR(40)
);

And when adding another one for phone numbers:

CREATE TABLE SUBCONTRACTSPHONENUMBER(
CIFSUBCONTRACT CHAR(9) not null CONSTRAINT FK_SUBCONTRACTS_CIF foreign key references SUBCONTRACTS(CIF),
PHONENUMBER NUMBER(11),
CONSTRAINT PK_SUBCONTRACTSPHONENUMBER_SUBCON_PHO PRIMARY KEY (CIFSUBCONTRACT,PHONENUMBER)
);

I get the error:

ORA-02253: constraint specification not allowed here.

The phone number and the CIF must be primary keys and the CIF is foreign of subcontracts .

Greetings.

Thank you very much.

sql oracle

asked by sergio rodriguez 30.11.2018 в 23:49

source

1 answer

SELECT A GRIDVIEW CELL IN ASP How to know if all the fields of a form are filled before submitting?

score 0 · Answer 1

You can not declare a constraint of type Foreign Key as part of the declaration of the column. This is reserved for primary keys, values default , check and probably something else that is directly associated with the column.

To declare a foreign key, you must do it after having completed the declaration of the column. In fact, I usually leave the declaration of primary and foreign keys for the end of create table , although the syntax does not require that.

Translating that into code, I would leave your sentence something like:

CREATE TABLE SUBCONTRACTSPHONENUMBER(
    CIFSUBCONTRACT CHAR(9) not null 
  , PHONENUMBER NUMBER(11)
  , CONSTRAINT PK_SUBCONTRACTSPHONENUMBER_SUBCON_PHO 
      PRIMARY KEY (CIFSUBCONTRACT,PHONENUMBER)
  , CONSTRAINT FK_SUBCONTRACTS_CIF 
     foreign key (CIFSUBCONTRACT) references SUBCONTRACTS(CIF)
);