0

I have created PERSON table in Oracle by this SQL syntax: 

 Create table person
 (
 p_id int not null,
 personName char(5) not null );

Then I am trying to create ORDERS table with the following syntax: 

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES person(p_id) );

But I am getting the following error . 

No matching unique or primary key for this column-list.

What is the problem ? How can I solve this ?

APC
  • 144,005
  • 19
  • 170
  • 281
osimer pothe
  • 2,827
  • 14
  • 54
  • 92
  • Do you mean [oracle] or [mysql]? Although Oracle Corporation owns MySQL (since it bought Sun) we reserve the [oracle] tag for the actual Oracle RDBMS, which is a different product. – APC Nov 14 '15 at 09:00
  • After due consideration the posted error message is an Oracle one, ORA-02270, so I have removed the [mysql] tag. Please revert if you want an answer for both databases. – APC Nov 14 '15 at 09:07
  • http://stackoverflow.com/search?q=[oracle]+No+matching+unique+or+primary+key+for+this+column-list –  Nov 14 '15 at 13:39

3 Answers3

1

Add primary key to person table:

 CREATE TABLE person(
   p_id int not null,
   personName char(5) not null,
   PRIMARY KEY (p_ID)
 );

SqlFiddleDemo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

Foreign keys enforce a one-to-many relationship. That is, however many records there are in the dependent table they can only reference a single record in the parent table. This means the referenced column(s) in the parent table must be constrained by a PRIMARY or UNIQUE key.

The error message is telling you that there is no such constraint on person(p_id). And lo! if we compare the two DDL statements you have posted we can see that you have created a primary key for ORDERS but not for PERSON.

The solution is simple: constrain P_ID by adding a primary key to PERSON. You can either drop and re-create the table, or you can use an alter table statement to add a primary key.

Community
  • 1
  • 1
APC
  • 144,005
  • 19
  • 170
  • 281
0

You should add primary key to person table. try this:

ALTER TABLE Person ADD CONSTRAINT p_id PRIMARY KEY (p_id);

SSD
  • 359
  • 1
  • 3
  • 15