You must Sign In to post a response.
  • Category: SQL Server

    Make FK column not to accept NULL values

    Hi ,
    I have a table with a Foreign key(Fk). Fk normally accepts NULL values. but in my case Fk has always values so I wanted to mark Fk column so that it should NOT allow NULL values.
    Please suggest how can do this.
    Thanks,
    Pramod
  • #763371
    Hello Pramod,

    Try this sample queries :

    CREATE TABLE Persons
    (
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (P_Id)
    )

    CREATE TABLE Orders
    (
    P_Id int
    )

    ALTER TABLE Orders
    ADD CONSTRAINT fk_PerOrders
    FOREIGN KEY (P_Id)
    REFERENCES Persons(P_Id)

    ALTER TABLE Orders
    ALTER COLUMN P_Id int NOT NULL


    Here i have created two tables named Persons with primary key and Orders without primary key. Than i have created constraint for foreign key of Orders table which refers to the primary key of Persons table.

    Than i have altered the foreign key column with not null constraint.


    Hope this will help you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #763425
    If the Key is mark as primary key in the other table then the foreign key will not accept the NULL value.

    You can also set the NOT NULL constraint so that it will not accept the null value.

    Thanks & Regards
    Anil Kumar Pandey
    Microsoft MVP, DNS MVM


Sign In to post your comments