1

Table One:

CREATE TABLE Customer 
(CustNo VARCHAR2(8) CONSTRAINT CustNoNotNull NOT NULL, 
 CustName VARCHAR2(30) CONSTRAINT CustNameNotNull NOT NULL, 
 Address VARCHAR2(50) CONSTRAINT AddressNotNull NOT NULL, 
 Internal CHAR(1) CONSTRAINT InternalNotNull NOT NULL, 
 Contact VARCHAR2(35) CONSTRAINT ContractNotNull NOT NULL, 
 Phone VARCHAR2(11) CONSTRAINT CPhoneNotNull NOT NULL, 
 City VARCHAR2(30) CONSTRAINT CityNotNull NOT NULL,
 State VARCHAR2(2) CONSTRAINT StateNotNull NOT NULL, 
 Zip VARCHAR2(10) CONSTRAINT zipNotNull NOT NULL,
  CONSTRAINT PK_CUSTOMER PRIMARY KEY (CustNo) ) ;

Table Two:

CREATE TABLE Facility
(FacNo VARCHAR2(8) CONSTRAINT FacNoNotNull NOT NULL, 
 FacName VARCHAR2(30) CONSTRAINT FacNameNotNull NOT NULL,
   CONSTRAINT PK_FACILITY PRIMARY KEY (FacNo) 
   CONSTRAINT Unique_FacName UNIQUE(FacName) );

Table Three:

CREATE TABLE EVENTREQUEST
( EVENTNO VARCHAR2(8) CONSTRAINT EVENTNONOTNULL NOT NULL, 
  DATEHELD DATE CONSTRAINT DATEHELDNOTNULL NOT NULL,
  DATEREQ DATE CONSTRAINT DATEREQNOTNULL NOT NULL,
  CUSTNO VARCHAR2(8) CONSTRAINT CUSTNONOTNULL NOT NULL ,
  FACNO VARCHAR2(8) CONSTRAINT FACNONOTNULL NOT NULL,
  DATEAUTH DATE CONSTRAINT DATEAUTHNULL NULL,
  STATUS VARCHAR2(10) CONSTRAINT STATUSNOTNULL NOT NULL,
  ESTCOST VARCHAR2(25) CONSTRAINT ESTCOSTNOTNULL NOT NULL,
  ESTAUDIENCE VARCHAR2(10) CONSTRAINT ESTAUDIENCENOTNULL NOT NULL,
  BUDNO VARCHAR2(8) CONSTRAINT BUDNONULL  NULL,
        CONSTRAINT PK_EVENTREQUEST PRIMARY KEY (EVENTNO),
        CONSTRAINT FK_CUSTNO FOREIGN KEY (CUSTNO) REFERENCES CUSTOMER (CUSTNO),
        CONSTRAINT FK_FACNO FOREIGN KEY (FACNO) REFERENCES FACILITY (FACNO),
        CONSTRAINT CHECK_EVENTREQUEST_STATUS CHECK(STATUS IN('PENDING','DENIED','APPROVED')));

I get this error executing Table Three:

"The specified constraint name has to be unique. *Action: Specify a unique constraint name for the constraint

How can I prevent this error from occurring?

Grant Miller
  • 27,532
  • 16
  • 147
  • 165
Tehuti Loka Ekema
  • 21
  • 1
  • I think that this is a duplicate of https://stackoverflow.com/questions/1397671/can-there-be-constraints-with-the-same-name-in-a-db. Essentially the constraint names have to be unique within the DB, not just unique within the table. At the very least you have two with the name CustNoNotNull. Personally, I generally leave out constraint names and allow the DB to auto-assign them. Another common pattern is to prepend the table name giving you CustomerCustNoNotNull and EventRequestCustNoNotNull. – Steven W. Klassen Sep 01 '18 at 22:37
  • Thanks @StevenW.Klassen i did prepend the constraint names and it created the table just like you suggested but like you said it's even better to let the DB auto-assign thanks again – Tehuti Loka Ekema Sep 01 '18 at 23:41
  • By the way, I'd advise [avoiding the `char` datatype](https://stackoverflow.com/a/42165653/230471). – William Robertson Sep 02 '18 at 10:11

1 Answers1

2

In Oracle, constraints are a type of object, and they have an identifier (a name by which they are distinguished from other objects). All constraints, on all tables within a schema, share the same name space. Which means you can't have two constraints with the same name in the same schema, even if they are on different tables.

Moreover, identifiers by default are case insensitive. On the second table you defined a constraint FacNoNotNull, and on the third table you are trying to define a constraint FACNONOTNULL. Since identifiers are case insensitive, this is the same name - so you get an exception.

A completely wrong approach (which would work, unfortunately - so many people may be inclined to do it even though it's wrong) is to enclose names in double-quotes, which makes them case sensitive. Don't do that!

Rather, one has to ask - why do you need to name your NOT NULL constraints in the first place? Just add the key words NOT NULL after the column definition; it is very hard to see when or where you would need to know the name of each such constraint.