sql :creating table in cmd

Question

SQL> create table reservation(
 reservationid varchar2(6) primary key,
 userid varchar2(6) references userprofile(userid),
 vehicleid varchar2(6) references vehicle(vehicleid),
 routeid varchar2(8) references route(routeid),
 bookingdate date not null,
 journeydate date not null,
 driverid varchar2(6) references driver(driverid),
 bookingstatus varchar2(20) not null,
 totalfare number(10) not null,
 boardingpoint varchar2(30) not null,
 droppoint varchar2(30) not null,
 vname varchar2(20) not null
);

i am getting an error: * ERROR at line 1:

ORA-02270: no matching unique or primary key for this column-list

• potashin: sqlfiddle.com/#!4
• Sam M: You've got an invalid foreign key reference in there. Without knowing the rest of your database schema it will be difficult to help pinpoint exactly. What you should try is eliminate one FK reference in the DDL and run it; keep eliminating a FK reference until it successfully runs. That should be the culprit.
• Tim Biegeleisen: @SamM It is worse than this as the REFERENCES keyword appears without an actual foreign key defintion.
• Sam M: @TimBiegeleisen that's what I thought too but then thought maybe it was an Oracle syntax I was unfamiliar with.
• jpw: @TimBiegeleisen With Oracle you can omit the foreign key part when the constraint is written inlined. So the syntax is correct.

Answer 1

The error is pretty clear. One of your foreign key references is pointing to a column in another table that is neither a primary key or unique.

So check the following, tables/columns, and make sure that they are the primary key, or that they at least have a unique constraint defined on them:

• userprofile.userid
• vehicle.vehicleid
• route.routeid
• driver.driverid

---

So for instance, the following simple example fails with exactly the error that you are getting:

create table groups (
 group_id number(10) not null -- not a pk or unique key.
)
/
create table users (
 user_id number(10) not null primary key,
 group_id number(10) references groups(group_id) -- this causes an error.
)
/

(削除) Demo: SQLFiddle. (削除ここまで)

Hmm, couldn't get a proper SQLFiddle link to a schema that has errors. The error that I got was like yours:

ORA-02270: no matching unique or primary key for this column-list

---

Making the groups.group_id a primary key, fixes the error:

create table groups (
 group_id number(10) not null primary key
)
/
create table users (
 user_id number(10) not null primary key,
 group_id number(10) references groups(group_id)
)
/

Demo: SQLFiddle.

---

Making the groups.group_id a unique key also fixes the error:

create table groups (
 group_id number(10) not null unique
)
/
create table users (
 user_id number(10) not null primary key,
 group_id number(10) references groups(group_id)
)
/

Demo: SQLFiddle.

EDIT: About the foreign key constraint syntax

The foreign key constraint syntax used by OP is valid and is documented here:

• inline_constraint
• references_clause