SQL Server foreign key constraints issue

Question

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.

Answer 1

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