CODEWITHSUNDEEP
c#sql-serverdatabase
Jesper Petersen
Jesper Petersen

Reputation: 89

Failure to make Foreign keys with two tables

I am in the process of building the website for the Linq, and the way that I need is to use Foreign keys to precisely set the same with my users table.¨

I have assured me that my Tabler has a primary key because it must be unique content that use grab.

Its a brugere table

CREATE TABLE [dbo].[brugere] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [username]    NVARCHAR (255) NOT NULL,
    [password]   NVARCHAR (255) NOT NULL,
    CONSTRAINT [PK_brugere] PRIMARY KEY ([Id]), 
    CONSTRAINT [FK_brugere_ToPoint] FOREIGN KEY ([Id]) REFERENCES [pointantal]([brugerid]), 
    CONSTRAINT [FK_brugere_ToKunde] FOREIGN KEY ([Id]) REFERENCES [KundeData]([brugerid])
);

Poinantal its here

CREATE TABLE [dbo].[pointantal] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [point]    INT            NOT NULL,
    [omrade]   NVARCHAR (255) NOT NULL,
    [datotid]  DATETIME       DEFAULT (getdate()) NOT NULL,
    [brugerid] INT            NOT NULL, 
    CONSTRAINT [PK_pointantal] PRIMARY KEY ([Id])
);

and KundeData table here

CREATE TABLE [dbo].[KundeData] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [Adresse]       NVARCHAR (255) NOT NULL,
    [Postnr]        INT            NOT NULL,
    [Mobil]         INT            NOT NULL,
    [Byen]          NVARCHAR (255) NOT NULL,
    [abonnementsId] INT            NOT NULL,
    [BuyDate]       DATETIME       DEFAULT (getdate()) NOT NULL,
    [prisid]        INT            NOT NULL,
    [HaevedeId]     NVARCHAR (255) NULL,
    [brugerid]      INT            NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

The error message I receive when I try to updater content is here

Update cannot proceed due to validation errors.
Please correct the following errors and try again.

SQL71516 :: The referenced table '[dbo].[pointantal]' contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column is a computed column, it should be persisted. SQL71516 :: The referenced table '[dbo].[KundeData]' contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column is a computed column, it should be persisted.

Upvotes: 1

Views: 231

Answers (2)

Millie Smith
Millie Smith

Reputation: 4604

A foreign key can only reference a primary key or unique column. You can either add a unique constraint to the columns that you are referencing:

CREATE TABLE [dbo].[pointantal] (
    ...
    CONSTRAINT AK_BrugerID UNIQUE(brugerid)

Or you can change your constraint to actually reference the primary key in your tables:

CONSTRAINT [FK_brugere_ToPoint] FOREIGN KEY ([Id]) REFERENCES [pointantal]([Id])

However, it seems like you really want the brugerid column of the pointantal and KundeData tables to access an Id (which is a unique column) in the brugere table. In this case, you put the foreign key on those tables and have it access the primary key of the bruger table. The following code runs sucessfully on my system:

CREATE TABLE [dbo].[brugere] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [username]    NVARCHAR (255) NOT NULL,
    [password]   NVARCHAR (255) NOT NULL,
    CONSTRAINT [PK_brugere] PRIMARY KEY ([Id])
);

CREATE TABLE [dbo].[pointantal] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [point]    INT            NOT NULL,
    [omrade]   NVARCHAR (255) NOT NULL,
    [datotid]  DATETIME       DEFAULT (getdate()) NOT NULL,
    [brugerid] INT            NOT NULL,
    CONSTRAINT [PK_pointantal] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_point_ToBrugere] FOREIGN KEY ([brugerid]) REFERENCES [brugere]([Id])
);

Upvotes: 3

A_Sk
A_Sk

Reputation: 4630

a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.

Try:

1>Change the Reference column

CREATE TABLE [dbo].[pointantal] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [point]    INT            NOT NULL,
    [omrade]   NVARCHAR (255) NOT NULL,
    [datotid]  DATETIME       DEFAULT (getdate()) NOT NULL,
    [brugerid] INT            NOT NULL, 
    CONSTRAINT [PK_pointantal] PRIMARY KEY ([Id])
);

CREATE TABLE [dbo].[KundeData] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [Adresse]       NVARCHAR (255) NOT NULL,
    [Postnr]        INT            NOT NULL,
    [Mobil]         INT            NOT NULL,
    [Byen]          NVARCHAR (255) NOT NULL,
    [abonnementsId] INT            NOT NULL,
    [BuyDate]       DATETIME       DEFAULT (getdate()) NOT NULL,
    [prisid]        INT            NOT NULL,
    [HaevedeId]     NVARCHAR (255) NULL,
    [brugerid]      INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id])
);

CREATE TABLE [dbo].[brugere] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [username]    NVARCHAR (255) NOT NULL,
    [password]   NVARCHAR (255) NOT NULL,
    CONSTRAINT [PK_brugere] PRIMARY KEY ([Id]), 
    CONSTRAINT [FK_brugere_ToPoint] FOREIGN KEY ([Id]) REFERENCES [pointantal]([Id]), 
    CONSTRAINT [FK_brugere_ToKunde] FOREIGN KEY ([Id]) REFERENCES [KundeData]([Id])
);

2>Change The Primary-Key

CREATE TABLE [dbo].[pointantal] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [point]    INT            NOT NULL,
    [omrade]   NVARCHAR (255) NOT NULL,
    [datotid]  DATETIME       DEFAULT (getdate()) NOT NULL,
    [brugerid] INT            NOT NULL, 
    CONSTRAINT [PK_pointantal] PRIMARY KEY ([brugerid])
);

CREATE TABLE [dbo].[KundeData] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [Adresse]       NVARCHAR (255) NOT NULL,
    [Postnr]        INT            NOT NULL,
    [Mobil]         INT            NOT NULL,
    [Byen]          NVARCHAR (255) NOT NULL,
    [abonnementsId] INT            NOT NULL,
    [BuyDate]       DATETIME       DEFAULT (getdate()) NOT NULL,
    [prisid]        INT            NOT NULL,
    [HaevedeId]     NVARCHAR (255) NULL,
    [brugerid]      INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([brugerid])
);

CREATE TABLE [dbo].[brugere] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [username]    NVARCHAR (255) NOT NULL,
    [password]   NVARCHAR (255) NOT NULL,
    CONSTRAINT [PK_brugere] PRIMARY KEY ([Id]), 
    CONSTRAINT [FK_brugere_ToPoint] FOREIGN KEY ([Id]) REFERENCES [pointantal]([brugerid]), 
    CONSTRAINT [FK_brugere_ToKunde] FOREIGN KEY ([Id]) REFERENCES [KundeData]([brugerid])
);

Upvotes: 0

Related Questions