Search code examples
sqloracle-databasealter

Oracle foreign key

So i have two tables

CREATE TABLE Client(
ID NUMBER(10) NOT NULL, (PRIMARY KEY)
Name VARCHAR(30) NOT NULL,
Surname VARCHAR(30) NOT NULL,
Phone NUMBER(11) NOT NULL,
Email VARCHAR(70));

CREATE TABLE Boss(
B_Surname VARCHAR(30) NOT NULL, (PRIMARY KEY)
B_Name VARCHAR(30) NOT NULL);

I need to put foreign key (B_surname from Boss table) to Client table. I've tried use ALTER TABLE:

ALTER TABLE Client ADD CONSTRAINT Boss_Client_fk FOREIGN KEY (B_Surname) REFERENCES Client(ID);

After that I got errors. Thanks for help.

Solution

  • I think you want the following. Please note that you should probably be using VARCHAR2 rather than VARCHAR in Oracle. They work the same at the moment but there is a possibility that Oracle will change the functionality for VARCHAR to bring it in line with the ANSI standard (in which empty strings '' are distinguished from NULLs ... but I digress):

    CREATE TABLE client
( id NUMBER(10) NOT NULL PRIMARY KEY
, name VARCHAR2(30) NOT NULL
, surname VARCHAR2(30) NOT NULL
, phone NUMBER(11) NOT NULL
, email VARCHAR2(70) );

CREATE TABLE boss
( b_surname VARCHAR2(30) NOT NULL PRIMARY KEY
, b_Name VARCHAR2(30) NOT NULL );

    And then:

    ALTER TABLE client ADD CONSTRAINT boss_client_fk FOREIGN KEY (surname) REFERENCES boss (b_surname);

    It's a bit odd that the column is named surname in CLIENT and b_surname in BOSS.

    If you want b_surname in BOSS to refer to surname in CLIENT -- then you need to make CLIENT.surname a primary key, or at least unique.