oracle - SQL Creating New Table that is based of a column in an already created table -

here example of need, different values: have table 1 created in database. table 1: person columns: pk->id, name, favorite color, favorite sport, etc.. table in database , filled values.

now want create second table, table 2 has primary key of favorite sport column table 1 , 1 more column description. ex: table 2: sports columns: pk->favorite sport, description

i want make sure creating table correctly, don't mess up. correct syntax use? (i fill data separately after table created.)

create table sports ( favorite_sport varcher(25), description varcher(100), primary key(favorite_sport), foreign key(favorite_sport) references person; )

thanks!

there several ways this, think i'd go with

create table sports   (sport        varchar2(25)      constraint pk_sports        primary key        using index,    description  varchar2(100)); 

(i changed name of primary key column on sports table sport).

you don't want nor can have sports.sport reference person.favorite_sport, favorite_sport not primary or unique key on person. instead, want foreign key relationship go other way around, person.favorite_sport referencing sports.sport:

alter table person   add constraint person_fk1     foreign key (favorite_sport) references sports(sport); 

sqlfiddle here

best of luck.