CODEWITHSUNDEEP
sqloracle-database
Antigoni
Antigoni

Reputation: 75

SQL Error: ORA-00904: : invalid identifier in CREATE AS SELECT

I am trying to create a table from 2 other tables in Oracle SQL Developer:

CREATE TABLE share_stock(
  share_id    NUMBER(6,0), 
  share_price NUMBER(10,2),
  company_id  NUMBER(6,0), 
  company_name VARCHAR2(50), 
  ticker_symbol VARCHAR2(4),
AS SELECT share_price.share_price_id, share_price.price, share_price.company_id, company.name, company.ticker_symbol 
FROM share_price, company
WHERE share_price.company_id = company.company_id,
CONSTRAINT sh_pk PRIMARY KEY (share_price.share_price_id),
CONSTRAINT sh_pr_fk FOREIGN KEY (share_price.share_price_id) REFERENCES share_price(share_price_id)
);

Basically I am trying to perform a CREATE AS SELECT, but I am getting this error:

Error at Command Line:294 Column:28 Error report: SQL Error: ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier"

I have tried to correct my syntax and I have not managed to get it right so far, Any ideas would be helpful,

Thanks in advance.

Upvotes: 0

Views: 7069

Answers (2)

user2873672
user2873672

Reputation: 1

CONSTRAINT sh_pk PRIMARY KEY (share_price.share_price_id), CONSTRAINT sh_pr_fk FOREIGN KEY (**share_price.**share_price_id) REFERENCES share_price(share_price_id) );

Your code doesn't work because ORACLE doesn't know what is share_price.shareprice_id??? You should write share_id, that is the column you want to be a primary key for the table share_stock.

Kind regards, MJ.

Upvotes: 0

Robert
Robert

Reputation: 25753

Try this way:

CREATE TABLE share_stock
AS 
SELECT SP.share_price_id as share_id , 
       SP.price as share_price, SP.company_id, C.name, 
       C.ticker_symbol 
FROM share_price SP 
join company C on SP.company_id = C.company_id;

alter table share_stock
add CONSTRAINT sh_pk PRIMARY KEY (share_id);

alter table share_stock
add CONSTRAINT sh_pr_fk FOREIGN KEY (share_id) 
REFERENCES share_price(share_price_id);

Upvotes: 4

Related Questions