CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Gleb
Gleb

Reputation: 1432

Insert statement error

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.

Upvotes: 1

Views: 117

Answers (3)

Manish Dalal
Manish Dalal

Reputation: 1796

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

Upvotes: 1

Ritesh Khatri
Ritesh Khatri

Reputation: 484

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

Upvotes: 0

Olesya Razuvayevskaya
Olesya Razuvayevskaya

Reputation: 1168

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.

Upvotes: 1

Related Questions