add a foreing key to an existing table

Question

I have to create a column in an existing table and at the same time create a foreing key with another table using this column that was created, and this foreing key by default has to be null. The way I’m doing it and this

 IF NOT EXISTS (SELECT * FROM SYSCOLUMNS C INNER JOIN SYSOBJECTS T ON C.id = T.id WHERE C.name = ('IdSetorPreparo') AND T.name = 'Produto')

    BEGIN
    ALTER TABLE Produto  
            ADD IdSetorPreparo int     
    END 

    ALTER TABLE Produto 
        ADD CONSTRAINT FK_Produto_IdSetorPreparo FOREIGN KEY(IdSetorPreparo) REFERENCES OrgSetor(id)  

Well, I was wondering if it’s possible at the same time to create the column to already create the foreing key.

Answer 1

In SQL Server you can do so:

ALTER TABLE Produto
    ADD IdSetorPreparo INTEGER,
    FOREIGN KEY(IdSetorPreparo) REFERENCES OrgSetor(id);

If you want the column to admit NULLS you just have to set it to NULL.

ADD IdSetorPreparo INTEGER NULL

In this particular case the Constraint will not be verified.