How to establish referential integrity constraint?

Question

    1 create table pointofinterest(
    2 pointid number not null,
    3 describe varchar(30),
    4 opentime varchar(30),
    5 closetime varchar(30),
    6 townid varchar(30),
    7 constraint pk primary key (pointid),
    8 constraint  fk foreign key(townid) references pointofinterest(townid));

I got an error like this:

  create table pointofinterest(
     *
   ERROR at line 1:
   ORA-00955: name is already used by an existing object

score 1 · Answer 1 · answered Apr 06 '20 at 06:17

1

Error is very much clear, you are trying to create the object with a name which is already there in your DB.

Actually You are creating 3 objects here, You need to change the name of one of the object:

pointofinterest
pk
fk

answered Apr 06 '20 at 06:17

Popeye

35,427
4
10
31

I agree with @Tejash. You should come up with a naming convention in your schema for things like constraints, triggers, indexes, and sequencees (If you are using them as PKs). Something like PK_(TABLENAME) or FK_(TABLENAME)_# (You can increment the number for each foreign key). Not only does this help prevent this error, but it makes triage of errors much easier. – Del Apr 06 '20 at 14:39
Can you please provide the related SQL with naming convention for more understanding ? – SP45 Apr 07 '20 at 02:46

score 0 · Answer 2 · answered Apr 06 '20 at 06:16

Using only a few columns from those tables (I'm too lazy to type them all):

SQL> create table town
  2    (townid   number primary key,
  3     townname varchar2(30)
  4    );

Table created.

SQL> create table pointofinterest
  2    (pointid  number primary key,
  3     describe varchar2(30),
  4     townid   number constraint fk_point_town references town (townid)
  5    );

Table created.

SQL>

What you did wrong is that you've tried to reference the same table (pointofinterest); it is allowed, no problem about it, but - that's not what model shows.

Error you got simply means that you already have an object with the same name so - you can't have two of them. That's probably the pointofinterest table itself or primary (or foreign) key constraint name.

Also, Oracle recommends to use varchar**2** datatype, not varchar.

I got an error again:townid number constraint fk point_town references town(townid)) * ERROR at line 7: ORA-00957: duplicate column name — SP45, Apr 06 '20 at 06:52
You got an error, and I can't debug code I can't see. If you can "translate" error message to plain English, it is pretty much obvious - you used the same column name **twice** in the same `create table` command, so - fix it. — Littlefoot, Apr 06 '20 at 06:59

score 0 · Answer 3 · answered Jul 22 '21 at 18:46

Check this one!

create table PointofInterest(
                            pointID number,
                            describe varchar2(30), 
                            opentime varchar(10),
                            closetime varchar2(10),
                            townID number,
                            constraint pk primary key(point ID)
);
create table Town(
                    townID number,
                    townname varchar(30), 
                    state varchar(30),
                    longitude varchar(30),
                    latitude varchar(30), 
                    summertemp number,
                    wintertemp number, 
                    sealevel number, 
                    constraint pk2 primary key (townID)
);

While your answer may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. You can edit your answer to add explanations and give an indication of what limitations and assumptions apply. - [From Review](https://stackoverflow.com/review/late-answers/29447382) — Adam Marshall, Jul 23 '21 at 22:00

How to establish referential integrity constraint?

3 Answers3