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

Asked

Viewed 13,323 times

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));

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

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

How can I add foreign key with ALTER TABLE correctly ?

sql oracle

2 answers

2

1


You don’t own the column cod_client in the OS table, add it and it will work.

Your scheme would look 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 column and foreign key at the same time:

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

  • Hello, can I add the Foreign key without first having to add the attribute to the table ? Without having to put the cod_client in the OS, and simply add with the alter table ?

  • You can do everything in one ALTER TABLE, where it creates the attribute and the Constraint at the same time, I will include in the answer.

  • 1

    Thank you for the most complete answer, +1 and correct answer.

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.