CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Laziale
Laziale

Reputation: 8225

Insert new row in sql table with same foreign key value

I need to be able to insert multiple rows in a table where one of the fields, a foreign key will be included in multiple rows.

Currently when I'm trying to do insert I'm getting this error:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Cannot insert duplicate key row in object 'dbo.userGroupMembership' with unique index 'IX_userId'. The duplicate key value is (264673).

Query I'm using:

INSERT INTO userGroupMembership(userId, usergroupId, created, adultAdminAccessLevel)
SELECT [userId], 12, GETDATE(), 0
  FROM [dbo].[userGroupMembership]
  where usergroupId = @UserGroupId

UserId is the foreign key field.

Any idea if I need to do any configuration change in the table or how can I be able to insert multiple rows with same foreign key?

Upvotes: 0

Views: 997

Answers (1)

Anthony Hancock
Anthony Hancock

Reputation: 931

You have a unique index allowing one row per userID. If you truly want more than one row per userID just drop the unique index.

DROP INDEX dbo.userGroupMembership.IX_userID;

Upvotes: 1

Related Questions