CODEWITHSUNDEEP
sqlsql-serverdatabase
Chesneycar
Chesneycar

Reputation: 563

SQL Server foreign key constraints issue

I have 2 tables :

  1. dog
  2. dogowner

I am trying to create a foreign key from DogOwner to Dog but not on the Primary Key of the dog table. I am planning to create my own unique id's for the dog table's dog_id column. Herewith the schemas :

CREATE TABLE dog(
    id          INT NOT NULL identity(1,1),
    dog_id      INT NOT NULL,
    dog_name    VARCHAR (200) NOT NULL,
    create_date DATETIME NOT NULL,
    update_date DATETIME DEFAULT getutcdate(),
    start_date  DATETIME DEFAULT getutcdate(),
    end_date    DATETIME DEFAULT getDate() - 101,
    is_active   BIT NOT NULL DEFAULT '0',
    PRIMARY KEY (id,dog_id)
    );

CREATE TABLE dogowner(
    dogowner_id  INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    dog_id       INT NOT NULL,
    FOREIGN KEY (dog_id) REFERENCES dog(dog_id)
    );

As soon as I create the foreign key constraint on the dogowner table it fails with the following error :

There are no primary or candidate keys in the referenced table 'dog' that match the referencing column list in the foreign key 'FK__dogowner__dog_id__00AA174D'.

> UPDATE :

So eventually I dropped the complicated Schema design and opted for history tables on every table that I want to version.So the dog table will have a dog_history or dog_log table with a post insert/update done on all the history tables.

This is not the way I wanted to do it but it allows me to have foreign key constraints in my database, soft deletes and logging of data. Thanks all for the input. I am following the KISS principle.

Upvotes: 3

Views: 217

Answers (2)

Milica Medic Kiralj
Milica Medic Kiralj

Reputation: 3780

The dog_id field needs to be unique field, the following will work:

create table dog(
id int not null identity(1,1),
dog_id int unique not null,
dog_name varchar(200) not null,
create_date datetime not null ,
update_date datetime default getutcdate(),
start_date datetime default getutcdate(),
end_date datetime default getDate() - 101,
is_active bit not null default '0',
primary key(id,dog_id)
);

create table dogowner(
dogowner_id int not null identity(1,1) primary key,
dog_id int not null,
foreign key(dog_id) references dog(dog_id)
);

From the MSFT documentation:

You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

Hope this helps

Upvotes: 1

user3714403
user3714403

Reputation: 101

First create unique key on dog_id field in dog table and create unique constraint on it and then you can refer this unique key as foreign key in Dog_owner table.

Upvotes: 0

Related Questions