CODEWITHSUNDEEP
sql-server-2008
afghifari
afghifari

Reputation: 27

How to Avoid Constraint SQL Server 2008

i have a problem. i got 2 tables, with 1 foreign key.

Table A and Table B

CREATE TABLE A
kdtrans int (5) primary key



INSERT INTO A Values
(1),(2),(3)

then i want to insert in table B

CREATE TABLE B
kdtrans int (5) primary key,
FOREIGN KEY kdtrans REFERENCES A(kdtrans)



INSERT INTO B Values
(1),(2),(3),(4),(5)

these query is error cause of constraint. so what should i do to avoid constraint. i wont to insert new record in table A.

Upvotes: 1

Views: 108

Answers (1)

Martin Smith
Martin Smith

Reputation: 452978

It is possible to do this by disabling the constraint but you might as well just drop it as the thing it is supposed to guarantee is no longer true.

To disable the constraint you can use

CREATE TABLE A
  (
     kdtrans INT PRIMARY KEY
  )

INSERT INTO A
VALUES      (1),
            (2),
            (3)

CREATE TABLE B
  (
     kdtrans INT PRIMARY KEY CONSTRAINT FK FOREIGN KEY REFERENCES A(kdtrans)
  )

ALTER TABLE B
  NOCHECK CONSTRAINT FK

INSERT INTO B
VALUES      (1),
            (2),
            (3),
            (4),
            (5)

To drop it you would use

ALTER TABLE B DROP CONSTRAINT FK

Upvotes: 1

Related Questions