Create the first user in a table with circular references

Question

I'm creating a user-based database for a login system. I have around 15 tables in my DB. I have a procedure that add a new user in the DB. I had created a fake user(which send internal communication) before i added the circular references in my DB, from there i created my first real user with the reference to my user 0. Then I've deleted all the accounts to repopulate it.

My table is design like this:

ID, password, ..., idSupervisor (which is the circular references)

I cannot add a new account since I don't have the first fake accounts to add a a references.

How can I add the fake account again without dropping all the database

I'm working with SQL Server 2008

EDIT: my table is created that way:

CREATE TABLE User
(
    ID           INT PRIMARY KEY IDENTITY,
    Pass         VARCHAR(100) NOT NULL,
    IdSupervisor INT,

    FOREIGN KEY (IdSupervisor) REFERENCES User(ID) -- Actually added after in an alter table
)

I don't want to drop the whole database. I just want to add a new fake user so i can start my user list from there.

just after i finished writting this question i stubbled onto [this post:](http://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql). Is it possible to integrate it into my procedure? — Chax, Aug 07 '16 at 17:24
@SunilKumar I didn't though it through when i ran my command. I was wanting to delete only the real account and i forgot about the fake user. Honest mistake.... — Chax, Aug 07 '16 at 17:30
What exactly is the question? Can't you just re-add the fake user? I don't see what the problem is here. Why do you even want "fake data" in the first place? — David, Aug 07 '16 at 17:33
So what you want now.. is there any problem with delete real accounts ? — Sunil Kumar, Aug 07 '16 at 17:46
@david I cannot re-add the fake user because i need to reference another user — Chax, Aug 07 '16 at 18:19
@Chax: Well what user did the *original* fake user reference? What user *should* it reference? — David, Aug 07 '16 at 18:19
@David I edited my question to add an exemple of my circular refenrence — Chax, Aug 07 '16 at 18:23
@Chax: I understand what a circular reference is. But nobody here knows what your business data *should* be. For the fake user, what value should logically go in that field? If there isn't one, then maybe the field should allow null values. — David, Aug 07 '16 at 18:24
@David It could be null, but only for the fake user. The way i see it, a fake user i a base user that the first real user will use as starting point. Is there a better way to create a user table that tracks who created the new user. A user should only be added by another user. — Chax, Aug 07 '16 at 18:27
@Chax: `"It could be null"` - Then what exactly are you asking? Insert a record with a `NULL` value in that field. What's the problem? — David, Aug 07 '16 at 18:28
@David I cannot because it references the table. If i do that i get an error in SSMS. It should be null for the fake user — Chax, Aug 07 '16 at 18:31
@Chax: `"i get an error in SSMS"` - What code do you use to insert the record and what error do you receive? — David, Aug 07 '16 at 18:34
@David CODE: `INSERT ITNO User(ID, Pass) VALUES (0, 'something') ` ErrorMessage: `Cannot insert the value NULL into column 'idSupervisor', table 'DB.dbo.User'; column does not allow nulls. INSERT fails.` — Chax, Aug 07 '16 at 18:44

score 1 · Accepted Answer · answered Aug 07 '16 at 19:01

After a lengthy comment thread on the question, it would appear that the main problem is this error message:

Cannot insert the value NULL into column 'idSupervisor', table 'DB.dbo.User'; column does not allow nulls. INSERT fails.

Given another of your comments:

It could be null

It would seem that all you need to do is allow NULL values for that column. Something like this:

ALTER TABLE [User] ALTER COLUMN [IdSupervisor] INT NULL

Simply alter the column to allow NULL values, then you can insert a record with a NULL value in that column.

Create the first user in a table with circular references

1 Answers1