Att ta bort ett FOREIGN KEY CONSTRAINT

Kodexempel som visar hur man gör ALTER TABLE DROP CONSTRAINT i MS SQL Server. Manualsidan för ALTER TABLE visar kommandots syntax.

http://msdn.microsoft.com/en-us/library/ms190273%28SQL.90%29.aspx

Kolla noga vilken tabell som är inbladad i respektive åtgärd. Kopiera koden och provkör om du är osäker.

--
-- Example on removing a foreign key constraint in SQL Server
--
DROP TABLE Mobile;
DROP TABLE Staff;

CREATE TABLE Staff
(
    id INT PRIMARY KEY,
    namn CHAR(10)
);

CREATE TABLE Mobile
(
    id INT PRIMARY KEY,
    idStaff INT
        CONSTRAINT fk_idStaff FOREIGN KEY (idStaff) REFERENCES Staff(id),
    number CHAR(10)
);

INSERT INTO Staff VALUES (1, 'Micke');
INSERT INTO Mobile VALUES (1, 1, '555-112');

--
-- Deleting Micke should not be possible, view error message for constraint name
--
DELETE FROM Staff WHERE id = 1;

--
-- Show foreign key constraints, look for column FK_NAME
-- Table staff has a foreign key constraint relation with table Mobile
--
exec sp_fkeys Staff;

--
-- Drop the foreign key constraint, in table Mobile, that references table Staff
-- Delete is now possible
--
ALTER TABLE Mobile DROP CONSTRAINT fk_idStaff;
DELETE FROM Staff WHERE id = 1;