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

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