Chax Chax - 3 months ago 13
SQL Question

Create the first user in a table with circular references

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 refernece 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 2010

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.

Answer

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.

Comments