Oracle Sql: foreign-key is also primary key syntax

foreign-keys, oracle, primary-key, sql, syntax-error

Solution

Use a named constraint, i.e.:

create table d_cats (
   an_id                        integer     primary key
 , feline_leukemia_test_date    date        not null
 , constraint d_cats_animals_fk foreign key (an_id) references d_animals (an_id)
 );

Problem

I just have a quick question about notation. I have two tables right now. This one has basic animal information: ``` create table d_animals ( an_id integer primary key , an_gender varchar2(1) not null , an_dob date not null , an_name varchar2(10) not null ); ``` This one is about cats: ``` create table d_cats ( an_id integer primary key , feline_leukemia_test_date date not null , an_id foreign key references d_animals_(an_id) ); ``` As you can see, I'm trying to use an_id as the primary key in d_cats but also refernce the an_id from the d_animals table. I'm getting the following error for d_cats: ``` ORA-00957: duplicate column name ``` So how do I correctly write this? Also, I don't want to create another column for d_cats. My professor wants us to write d_cats with only an_id and feline_leukemia_test_Date. Thanks.

Original source