CODEWITHSUNDEEP
sqloracle-databaseforeign-keys
sonali patil
sonali patil

Reputation: 23

ERROR :ORA-02264: name already used by an existing constraint

create table ward
(
wnum int primary key,
wname varchar(30),
phoneno int,
wloc varchar(50),
chnursename varchar(20) constraint ward_fk references charge_nurse
);

create table charge_nurse
(
chnurse varchar(20) constraint charge_nurse_pk primary key,
stnum int constraint charge_nurse_fk references staff
);

create table staff
(
stname varchar(20),
stnum int constraint staff_pk primary key,
addr varchar(20),
phoneno int,
stposition varchar(30),
specality varchar(30) unique,
shift varchar(10),
noofhoursperweek int
);

create table generalsupplies
(
itnum int constraint generalsupplies_pk primary key,
itname varchar(20) unique,
quantityinstock int,
reorder varchar(10),
despt varchar(10),
costperunit int
);

create table pharmasupplies
(
dnum int constraint pharmasupplies_pk primary key,
dname varchar(30) unique,
despt varchar(20),
dosage_Mg int,
quantityinstock int,
reorder varchar(10),
costperunit int
);

While creating the below table I am facing problem:

ORA-02264: name already used by an existing constraint

create table centralsupplies
(
wardnum int constraint centralsupplies_fk references ward,
itemnum int constraint centralsupplies_fk references generalsupplies,
drugnum int constraint centralsupplies_fk references pharmasupplies,
quantity_required varchar(20),
staffname varchar(10) references staff(stname),
staffnum int constraint centralsupplies_fk references staff,
regnum int unique,
dateord date,
daterec date
);

How do I solve this problem?

Upvotes: 0

Views: 1010

Answers (2)

APC
APC

Reputation: 146269

Your create table statement has four foreign keys all called centralsupplies_fk. That is not allowed: constraint names must be unique within a schema. You must give each one a different name.

It is usual practice to include the referenced table in the key name. So

create table centralsupplies
(
wardnum int constraint centralsupplies_ward_fk references ward,
itemnum int constraint centralsupplies_generalsupplies_fk references generalsupplies,
drugnum int constraint centralsupplies_pharmasupplies_fk references pharmasupplies,
quantity_required varchar(20),
staffname varchar(10) references staff(stname),
staffnum int constraint centralsupplies_staff_fk references staff,
regnum int unique,
dateord date,
daterec date
)

Also you have another foreign key constraint on STAFFNAME which you have not named. You do not need to name constraints, the system will generate a unique one for you, but it's generally a good idea to name them, not least because it is easier to diagnose relational integrity error messages with meaningfully named constraints.

However, in this case the correct solution is to drop the STAFFNAME column. You already have a foreign on the STAFF table, and you should join to that table whenever you need to display a value for STAFFNAME. Besides you do not have a unique constraint on staff.stname column, so the foreign key statement will fail: foreign keys can only reference primary or unique keys.

Upvotes: 0

Olivier Depriester
Olivier Depriester

Reputation: 1625

You use 3 times the same constraint name centralsupplies_fk in your centralsupplies table.

3 constraints = 3 constraint names

Upvotes: 1

Related Questions