Insert statement error

Question

I have table dbo.Students_Old with columns :

Name(varchar), DocNum(int), Group(varchar), Form(varchar).

It have no primary key and I need to normalize this table. So I am going to make three tables:

dbo.Students(StudentId(int primary key), Name(varchar), Group(int fk), Form(int fk),

dbo.Forms(FormId(int pk), FName(varchar)), dbo.Groups(GroupId(int pk), GName(varchar)).

And also I need to fill all this tables with data from dbo.Students_Old. My code is:

CREATE TABLE dbo.Groups(GroupId int IDENTITY(1,1) PRIMARY KEY, GName nvarchar(10));

GO

INSERT INTO dbo.Groups(GName) 
  select Group
  from dbo.Students_Old
  group by Group

GO

CREATE TABLE dbo.Forms(FormId int IDENTITY(1,1) PRIMARY KEY, Form nvarchar(20));

INSERT INTO dbo.Forms(Form) 
select Form
from dbo.Students_Old
group by Form

GO

CREATE TABLE dbo.Students (StudentId int PRIMARY KEY, Name nvarchar(50),
Form int NOT NULL,
Group int NOT NULL,
 CONSTRAINT Form FOREIGN KEY(StudentId) REFERENCES dbo.Forms(FormId),
 CONSTRAINT Grup FOREIGN KEY(StudentId) REFERENCES dbo.Groups(GroupId));

GO

INSERT INTO dbo.Students(StudentId, Name, Form, Group) 
select DocNum, Name, f.FormId, g.GroupId 
from dbo.Students_Old s
join dbo.Forms f on s.Form=f.Form
join dbo.Groups g on s.Group=g.GName

Also Students_Old.DocNum is unique.

Tables creates normaly, but on the insert statement i have an error:

The INSERT statement conflicted with the FOREIGN KEY constraint "Form". The conflict occurred in database "DBNames", table "dbo.Forms", column 'FormId'.

Help me please.

http://stackoverflow.com/questions/2965837/insert-statement-conflicted-with-the-foreign-key-constraint — Nagaraj S, Apr 29 '14 at 05:27
Did you really mean to have `StudentID` as a FK to `Forms` and `Groups`? Your text says you should have two other columns in `Student` that is FK to `Forms` and `Groups`. — Mikael Eriksson, Apr 29 '14 at 05:53
How about having two columns in `Student` `FormID` that is a FK to `Forms` and `GroupId` that is a FK to `Groups`? That is what you described in your text but it is not what your code says. — Mikael Eriksson, Apr 29 '14 at 06:01
`CONSTRAINT Form FOREIGN KEY(FormId) REFERENCES dbo.Forms(FormId), CONSTRAINT Grup FOREIGN KEY(GroupId) REFERENCES dbo.Groups(GroupId));` — Mikael Eriksson, Apr 29 '14 at 06:02
He is already having those columns for foreign key, only their names are Form and Group (both int type) — Manish Dalal, Apr 29 '14 at 06:13

score 1 · Answer 1 · answered Apr 29 '14 at 05:35

Not sure if that is the case, since you get the FOREIGN KEY error, but try avoiding column names like GROUP or other reserved words. While you can avoid errors on table creation step, you can face serious problems during modifications/updates on such tables.

score 1 · Accepted Answer · answered Apr 29 '14 at 06:11

Execute the below query on a new database

CREATE TABLE dbo.Groups(GroupId int IDENTITY(1,1) PRIMARY KEY, GName nvarchar(10));

GO

INSERT INTO dbo.Groups(GName) 
  select Group
  from dbo.Students_Old
  group by Group

GO

CREATE TABLE dbo.Forms(FormId int IDENTITY(1,1) PRIMARY KEY, Form nvarchar(20));

INSERT INTO dbo.Forms(Form) 
select Form
from dbo.Students_Old
group by Form

GO

CREATE TABLE dbo.Students (StudentId int PRIMARY KEY, Name nvarchar(50),
Form int NOT NULL,
[Group] int NOT NULL,
 CONSTRAINT Form FOREIGN KEY(Form) REFERENCES dbo.Forms(FormId),
 CONSTRAINT Grup FOREIGN KEY(Group) REFERENCES dbo.Groups(GroupId));

GO

INSERT INTO dbo.Students(StudentId, Name, Form, Group) 
select DocNum, Name, f.FormId, g.GroupId 
from dbo.Students_Old s
join dbo.Forms f on s.Form=f.Form
join dbo.Groups g on s.Group=g.GName

I've changed to following lines

 CONSTRAINT Form FOREIGN KEY(Form) REFERENCES dbo.Forms(FormId),
 CONSTRAINT Grup FOREIGN KEY([Group]) REFERENCES dbo.Groups(GroupId));

In your code the foreign key is made on StudentID column

score 0 · Answer 3 · answered Apr 29 '14 at 05:57

execute below query.

CREATE TABLE dbo.Groups(GroupId int IDENTITY(1,1) PRIMARY KEY, GName nvarchar(10));

GO

INSERT INTO dbo.Groups(GName) 
  select [Group]
  from dbo.Students_Old
  group by [Group]

GO

CREATE TABLE dbo.Forms(FormId int IDENTITY(1,1) PRIMARY KEY, Form nvarchar(20));

INSERT INTO dbo.Forms(Form) 
select Form
from dbo.Students_Old
group by Form

GO

CREATE TABLE dbo.Students (StudentId int PRIMARY KEY, Name nvarchar(50),
Form int NOT NULL,
[Group] int NOT NULL,
 CONSTRAINT Form FOREIGN KEY(StudentId) REFERENCES dbo.Forms(FormId),
 CONSTRAINT Grup FOREIGN KEY(StudentId) REFERENCES dbo.Groups(GroupId));

GO

INSERT INTO dbo.Students(StudentId, Name, Form, [Group]) 
select DocNum, Name, f.FormId, g.GroupId 
from dbo.Students_Old s
join dbo.Forms f on s.Form=f.Form
join dbo.Groups g on s.[Group]=g.GName

Insert statement error

3 Answers3