sql oracle, ALTER TABLE error

Question

Here is ERD model of my DB: erd

Here is create table codes i used so far: pastebin

I have created all tables and now I'm trying to alter them to create foreign keys,
but this alter doesn't work.

Alter table uskladnena_surovina add  foreign key  (datum_spotreby,id_dodavatela,id_suroviny) references ponukana_surovina    (datum_spotreby,id_dodavatela,id_suroviny) 
/

It says:

"DATUM_SPOTREBY": 
00904. 00000 -  "%s: invalid identifier"

I strongly recommend you to post question with variables columns and whatever named in English so you would get faster reply. Nevertheless it is not good idea to make Foreign key in date data type. Change to int maybe? — Marek
– Marek, Commented May 10, 2014 at 9:06
I will in the future use English, thanks. I added my CREATE TABLE code i used so far. Do you think that using DATE as foreign key makes this error? I googled for it and there wasnt any reason why i couldn't use it. — user3263155
– user3263155, Commented May 10, 2014 at 9:12
i forgot to add datum_Spotreby into uskladnena_surovina so obviously it couldnt work, now it works — user3263155
– user3263155, Commented May 10, 2014 at 9:18

Luke Woodward · Accepted Answer · 2014-05-10 09:10:52Z

2

You need to tell Oracle that you want to create a constraint. To do this, you need to specify add constraint after alter table .... If you don't tell Oracle what you want to add to the table, it assumes you want to add a new column.

Try

Alter table uskladnena_surovina add constraint constraint_name foreign key  (datum_spotreby,id_dodavatela,id_suroviny) references ponukana_surovina    (datum_spotreby,id_dodavatela,id_suroviny)

/

You also have to give the constraint a name. Feel free to change constraint_name.

answered May 10, 2014 at 9:10

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3263155 Over a year ago

i forgot to add datum_Spotreby into uskladnena_surovina so obviously it couldnt work, now it works BTW, why is constraint necessary here? Wouldnt it work without it?

user3263155 · Accepted Answer · 2014-05-10 09:17:55Z

0

Oh sorry guys, i forgot to add datum_Spotreby into uskladnena_surovina so obviously it couldnt work, now it works

answered May 10, 2014 at 9:17

user3263155

2033 silver badges12 bronze badges

Collectives™ on Stack Overflow

sql oracle, ALTER TABLE error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related