CODEWITHSUNDEEP
sqlsql-servert-sqlforeign-keys
Paul L
Paul L

Reputation: 97

SQL : Self referencing row

I have a Users table:

Create table dbo.Users (
    Id int IDENTITY (1, 1) PRIMARY KEY,
    ManagerId int FOREIGN KEY references dbo.Users(Id)
);

Users have Managers.

Managers are themselves Users.

Is it possible to have ManagerId NOT NULL and insert a row telling SQL it references itself? i.e. ManagerID = Id?

PS: I already know I can do it in two steps with ManagerID NULL, I also know I could code my app so that NULL means the user is managing himself, but that's not the point of my question.

Upvotes: 0

Views: 696

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

With an identity column, I don't think so. But we've had sequences for a long time. You could define your table like this:

create sequence [SQ_Users] as int start with 1;

create table dbo.Users (
    Id int NOT NULL 
        constraint [PK_Users] PRIMARY KEY
        constraint [DF_UserID] DEFAULT (next value for [dbo].[SQ_Users]),
    ManagerId int not null
        constraint [FK_User_Manager] FOREIGN KEY references dbo.Users(Id)
);

(Note: I named your constraints because I so loathe system-named ones; it's a service I provide). Now, if you want to have a user be their own manager, you do it like this:

declare @UserId int = next value for [dbo].[SQ_Users];
insert into dbo.Users (ID, ManagerID) values (@UserID, @UserID);

Note that this doesn't take away the common use case of the table itself auto-generating its own ID. That is, you can still do:

insert into dbo.Users (ManagerID) values (@ManagerID);

For what it is worth, it is a common idiom in hierarchical data (like what you have) that a NULL value for the parent ID means that the element is at the top or references itself. That is, your current setup shouldn't cause any sideways glances from "the next guy".

Upvotes: 2

Related Questions