How to add foreign key with constraint and alter table in Oracle?

Question

How to add foreign key with constraint and alter table in Oracle?

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have the following tables:

Create table OS(
nro_os      number(4),
data_os        date ,
hora_os        number(4)
);

Create table cliente(
cod_cliente       number(4),
nome_cliente      varchar(12),
endereco_cliente  varchar(20),
telefone_cliente  number(11)
);

And I have the following ALTER TABLE:

Alter table OS
add (constraint cliente_cod_cliente_fk foreign key (cod_cliente) references cliente (cod_cliente));

But when I run the query, it returns the error:

ORA-00904: "COD-CLIENT": Invalid identifier

How can I add the foreign key with ALTER TABLE correctly?

sql oracle

asked by anonymous 29.09.2017 / 19:50

2 answers

2

The command is very simple, I believe your problem may be in parenthesis, so test it:

ALTER TABLE OS
ADD CONSTRAINT cliente_cod_cliente_fk 
  FOREIGN KEY (cod_cliente)
  REFERENCES supplier(cod_cliente);

More questions: link

29.09.2017 / 19:56

Give hide or show when clicking another element Is it possible to change a user-defined table type?

score 1 · Accepted Answer

You do not have the client_client column in the OS table, add it and it will work.

Your scheme looks like this:

Create table OS(
nro_os      number(4),
data_os        date ,
hora_os        number(4),
cod_cliente number(4)
);

Create table cliente(
cod_cliente       number(4) primary key,
nome_cliente      varchar(12),
endereco_cliente  varchar(20),
telefone_cliente  number(11)
);

ALTER TABLE OS
ADD CONSTRAINT cliente_cod_cliente_fk 
  FOREIGN KEY (cod_cliente)
  REFERENCES cliente(cod_cliente);

To insert the column and the foreign key at the same time:

ALTER TABLE OS
ADD cod_cliente number(4) CONSTRAINT cliente_cod_cliente_fk 
  REFERENCES cliente(cod_cliente);