CODEWITHSUNDEEP
sqloracle-database
agam2104
agam2104

Reputation: 23

How to define a foreign key

my two parents table are person and artwork. I created their table as follows: 

create table person (
     person_id number(20) primary key,
     name varchar2(20),
     address varchar(50),
     contact_number number (10)
     );

and other 

 create table artwork (
    artwork_id number primary key,
    barcode char (20),
    title varchar2(20),
    description varchar2(50));

When I try to make the child table vote it is giving this error. 

   create table vote 
    (
        vote_id number(7) NOT NULL,
        artwork_id number(20),
        person_id number(20),

        PRIMARY KEY (vote_id), 
        FOREIGN KEY (artwork_id) REFERENCES artwork(artwork_id), 
        FOREIGN KEY ( person_id) REFERENCES Person(person_id)
    );

SP2-0734: unknown command beginning "FOREIGN KE..." - rest of line ignored.

Upvotes: 0

Views: 208

Answers (2)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

You forgot to mention constraint before foreign key:

create table vote 
(
    vote_id number(7) NOT NULL,
    artwork_id number(20),
    person_id number(20),
    PRIMARY KEY (vote_id), 
    CONSTRAINT fk_artwork_id FOREIGN KEY (artwork_id) REFERENCES artwork(artwork_id), 
    CONSTRAINT fk_person_id FOREIGN KEY ( person_id) REFERENCES Person(person_id)
);

Syntax

The syntax for creating a foreign key using a CREATE TABLE statement is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

Demo

http://sqlfiddle.com/#!4/3d141

Explanation

By default SQLPlus doesn't like blank lines. However we can easily configure our SQLPlus environment to ignore them:

SQL>  set sqlblanklines on

We can also put the setting in our glogin.sql file (assuming we're allowed to edit it, which isn't always the case).

Upvotes: 3

John
John

Reputation: 76

remove that blank line before the definition of your constraints when creating the vote table:

create table vote 
(
    vote_id number(7) NOT NULL,
    artwork_id number(20),
    person_id number(20),
    PRIMARY KEY (vote_id), 
    FOREIGN KEY (artwork_id) REFERENCES artwork(artwork_id), 
    FOREIGN KEY ( person_id) REFERENCES Person(person_id)
);

Any blank rows will stop SQL*Plus from accepting input lines.

If you want it to avoid this run:

  set sqlblanklines on

before any other instruction.

Upvotes: 1

Related Questions