Geek asks and answers

How to insert a row into a table with a foreign key?

I have a table that has a foreign key for itself. The parentid column is a foreign key, and cannot be NULL.

if I do INSERT INTO mytable(name) VALUES('name'), so it says that it cannot insert NULL into parentid . BUT, what value can I set for it if a row has not yet been inserted ?!

How can I write a script that will add a row to this table?

thank

+3
source share
7 answers

Trick: enter a dummy string with a dummy key, say 99999. Insert with this as FK, and then change FK to its real value. And do it in a transaction.

+5

NOT NULL, . ParentId, NULL . , , .

+4

id parentid -1:

CREATE TABLE mytable(
    id int NOT NULL IDENTITY,
    parentid int  NOT NULL,

    PRIMARY KEY (id),
    FOREIGN KEY (parentid) REFERENCES mytable(id)
)  ;


SET IDENTITY_INSERT mytable ON ;      <-- this allows you to insert the 
INSERT INTO mytable(id, parentid)     <-- auto incremented identity field
    VALUES (-1, -1);
SET IDENTITY_INSERT mytable OFF ;

SELECT * FROM mytable ;

| id | parentid |
| -1 | -1       |

, , IDENTITY_INSERT ON, OFF.

, , NOT NULL parentid.

+2

, null, fk null.

, , ,

CREATE TABLE mytable
(
 id int IDENTITY(1,1) primary key,
 name varchar(50) not null,
 parentid int not null
)
go
alter table mytable
add constraint FK_mytable_parentid FOREIGN KEY ( parentid ) references mytable(id)

ALTER TABLE mytable alter column parentid int null;

insert into mytable(name)
select 'test'

update mytable
set parentid = SCOPE_IDENTITY()
where id = SCOPE_IDENTITY()

ALTER TABLE mytable alter column parentid int not null;

select * from mytable
drop table mytable
+1

, , , , .

:

1 34 'name1' 34, 35 'name2' 34 (id, name, parentid)

create table Table1
(
    id int not null primary key,
    name varchar(20) not null,
    parentId int not null
)

insert Table1
values
    (34, 'name1', 34),
    (35, 'name2', 34)

:

create table Table2
(
    id int identity(1, 1) primary key,
    name varchar(20) not null,
    parentId int not null foreign key references Table2(id)
)

:

set identity_insert Table2 on

insert Table2(id, name, parentId)
select *
from  Table1

set identity_insert Table2 on

[]

, :

alter table Table2
    add oldId int null

alter table Table2
    alter column parentId int null
go

insert Table2(name, oldId)
select name, id
from  Table1

update tt3
set parentId = tt2.id
from Table2 tt3
    join Table1 tt1 on
        tt1.id = tt3.oldId
    join Table2 tt2 on
        tt1.parentId = tt2.oldId

alter table Table2
    drop column oldId

alter table Table2
    alter column parentId int not null
+1

FK. (?) PK-ID Self-FK FK.

LIke so:

ALTER TABLE [Client] NOCHECK CONSTRAINT [FK_Client_MainClient]
INSERT INTO Client VALUES ...
@ClientID = SCOPE_IDENTITY()
IF @IsMainClient=1  
BEGIN
    UPDATE [Client] 
    SET MainClientID = @ClientID 
    WHERE ClientID = @ClientID 
END 
ALTER TABLE [Relatie] WITH CHECK CHECK CONSTRAINT [FK_Relatie_Relatie]
+1

Do not reference the IDENTITY column as a foreign key for self-binding. Use an alternate table key instead. I assume that you are using IDENTITY as a surrogate key, but each table should also have a natural key, so the IDENTITY column should not be the only key in your table.

0
source

More articles:


All Articles