CODEWITHSUNDEEP
sqlsql-server
user1491884
user1491884

Reputation: 649

Make a column required if other column is of certain value

What I have:

CREATE TABLE [dbo].[User]
(
    [id] INT NOT NULL PRIMARY KEY, 
    [name] VARCHAR(50) NOT NULL, 
    [postcode] INT NOT NULL, 
    [phone] INT NULL
)

What I want is that the phone number is required ONLY if the postcode is higher than 40000. If postcode is smaller than 40000, user can insert the phone number, although it is not required.

How do I do this?

Upvotes: 2

Views: 2455

Answers (2)

Gauravsa
Gauravsa

Reputation: 6524

You can use a check constraint:

CREATE TABLE [dbo].[User]
(
    [id] INT NOT NULL PRIMARY KEY, 
    [name] VARCHAR(50) NOT NULL, 
    [postcode] INT NOT NULL, 
    [phone] INT NULL, 
    CONSTRAINT CHK_Postcode CHECK (postcode >= 4000 OR Phone IS NOT NULL)
);

Upvotes: 7

Sourav Mehra
Sourav Mehra

Reputation: 445

This needs to be handled from the front end inserting values into the Database. Insert Query on one column based on another in the DB for the same table is not possible.

Upvotes: 0

Related Questions