CODEWITHSUNDEEP
sqlfirebirdfirebird2.5
user3677216
user3677216

Reputation: 29

Firebird foreign key and references from another table

So I have a problem with adding foreign key to my table "Tekmovalec". I would like to add new key to that table by using references from another table called "Kraj". Somehow I always get error when I try to add foreign key, so I made new database again and copied all commands I have done so far, here. So Does anyone have any idea why this doesn't work?

making new database: 

SQL> create database "c:\Baze\Tekmovanje.fdb" user 'sysdba' password 'masterkey';

SQL> create domain Rezultat as integer default 18 not null
CON> check (value between 1 and 32);
SQL>

SQL> create table Tekmovalec(
CON> TekmovalecID integer not null,
CON> ime char(10) not null,
CON> priimek char(20) not null,
CON> vzdevek char(10),
CON> rojen date,
CON> tocke rezultat,
CON> primary key (tekmovalecID));
SQL>

SQL> create table Kraj(
CON> krajID int not null,
CON> imekraja char(20) not null,
CON> primary key(krajID));
SQL>

SQL> alter table Tekmovalec add foreign key (krajID) references kraj (krajID)
CON> on delete no action on update cascade;
SQL>

and then I get this error when I hit enter after alter table Tekmovalec add foreign key ....

Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-Unknown columns in index RDB$FOREIGN3
SQL>

Upvotes: 1

Views: 2777

Answers (1)

Ambrish
Ambrish

Reputation: 3677

Before adding foreign key relation, it is necessary to have joining column present in both the tables.

krajID column is present in Kraj table but it is missing in Tekmovalec table.

There are 2 thing that 2 can do:

  1. Drop table Tekmovalec and recreate it by adding krajID column. Like:

    create table Tekmovalec(
    ....
    krajID int,
    primary key (tekmovalecID));

  2. Alter the existing table and add new column. Like:

    alter table Tekmovalec add krajID int

Add new column to table

Upvotes: 1

Related Questions