Foreign key fails. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Foreign key fails.

Author

Topic

ThomasWD
Starting Member

4 Posts

Posted - 2008-02-01 : 04:24:12

I tried to make a Foreign key between "fornavn" and "efternavn".
Can someone see what i do wrong?

use master
GO
IF EXISTS (SELECT name FROM sysdatabases WHERE name = 'Navn')
DROP DATABASE Navn
GO
CREATE DATABASE Navn
GO
use NAvn
GO
SET DATEFORMAT dmy
GO
-------------------------------------------------------------------
CREATE TABLE Fornavn
(
fornavn_id INT IDENTITY PRIMARY KEY NOT NULL,
for_navn CHAR(50) NOT NULL,
efternavn_id INT REFERENCES Efternavn(efternavn_id) NOT NULL
);

CREATE TABLE Efternavn
(
efternavn_id INT IDENTITY PRIMARY KEY NOT NULL,
efter_navn Char(50) NOT NULL,
fornavn_id INT REFERENCES Kunder(kunde_id) NOT NULL
);

Insert into Fornavn
(for_navn)
Values
('Thomas')

Insert into Efternavn
(efter_navn)
Values
('Westergaard')

Here is the error from log.

Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK__Fornavn__efterna__7D78A4E7' references invalid table 'Efternavn'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-01 : 04:39:58

create the table efternavn first. but then you'll get the problem with your other foreign key to the kunder table.

Em

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-01 : 04:44:12

As message clearly states "Foreign key 'FK__Fornavn__efterna__7D78A4E7' references invalid table 'Efternavn'.", when your table Fornavn is getting created , Efternavn doesnot exist. therefore, the message. Create Efternavn table first, then add references.Also, not sure if you can have 2 create tables in a single batch, see BOL for this.As far as i think you need to have a GO statement between two table creation.

ThomasWD
Starting Member

4 Posts

Posted - 2008-02-01 : 04:46:06

Argh. the Kunder should be named "fornavn" and kunde_id fornavn_id

i think i mixed 2 projects tohgether

ThomasWD
Starting Member

4 Posts

Posted - 2008-02-01 : 04:47:13

Just like this

use master
GO
IF EXISTS (SELECT name FROM sysdatabases WHERE name = 'Navn')
DROP DATABASE Navn
GO
CREATE DATABASE NAvn
GO
use NAvn
GO
SET DATEFORMAT dmy
GO
-------------------------------------------------------------------
CREATE TABLE Fornavn
(
fornavn_id INT IDENTITY PRIMARY KEY NOT NULL,
for_navn CHAR(50) NOT NULL,
efternavn_id INT REFERENCES Efternavn(efternavn_id) NOT NULL
);

CREATE TABLE Efternavn
(
efternavn_id INT IDENTITY PRIMARY KEY NOT NULL,
efter_navn Char(50) NOT NULL,
fornavn_id INT REFERENCES Fornavn(fornavn_id) NOT NULL
);

Insert into Fornavn
(for_navn)
Values
('Thomas')

Insert into Efternavn
(efter_navn)
Values
('Westergaard')

ThomasWD
Starting Member

4 Posts

Posted - 2008-02-01 : 04:51:51

i tried to set "GO" between the 2 table creations.

then i get this error :s

Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK__Fornavn__efterna__7D78A4E7' references invalid table 'Efternavn'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 2
Foreign key 'FK__Efternavn__forna__00551192' references invalid table 'Fornavn'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-02-01 : 05:05:55

create your tables first, so you won't get the errors about them not existing when you try and create the FK's. then create the foreign keys afterwards with 'alter table', like this...

ALTER TABLE Fornavn
ADD CONSTRAINT fk_GiveItAName FOREIGN KEY (efternavn_id)
REFERENCES Efternavn(efternavn_id)

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources