CODEWITHSUNDEEP
sqlsql-server
kingcobra1986
kingcobra1986

Reputation: 971

SQL - When creating a table, how can I make it so a column is required when another column meets a certain requirement?

I am using Microsoft SQL Server Management Studio. So this is what I have right now:

CREATE TABLE Product 
(
    product_id          INT             NOT NULL    PRIMARY KEY IDENTITY,
    product_code        CHAR(4)         NOT NULL,   --For a book use  'BOOK'
    product_name        VARCHAR(40)     NOT NULL,
    product_desc        VARCHAR(5000),
    book_author         INT,
    book_publisher      INT,
    product_price       SMALLMONEY      NOT NULL    CHECK (product_price >= 0),     
    FOREIGN KEY (book_author)       REFERENCES Author
);

So I would like to make it so that book_author and book_publisher cannot be null if product_code == 'BOOK'.

Is this possible and how?

Upvotes: 3

Views: 7394

Answers (2)

shadow
shadow

Reputation: 1903

CREATE TABLE [dbo].[Product](
    [product_id] [int] IDENTITY(1,1) NOT NULL,
    [product_code] [char](4) NOT NULL,
    [product_name] [varchar](40) NOT NULL,
    [product_desc] [varchar](5000) NULL,
    [book_author] [int] NULL,
    [book_publisher] [int] NULL,
    [product_price] [smallmoney] NOT NULL,
    PRIMARY KEY CLUSTERED ( [product_id] ASC )
)
Go

ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product] CHECK  (  
    (
        ( [product_code] <> 'BOOK' )
        OR
        (
            ( [book_author] IS NOT NULL ) 
            AND ( [book_publisher] IS NOT NULL )          
        ) 
    )    
    AND         
    ( [product_price] >= 0 )
)
Go

ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [CK_Product]
Go

One constraint pair row.

Upvotes: 4

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

This is how I would do it:

CREATE TABLE Product (
  product_id INT NOT NULL PRIMARY KEY IDENTITY
  , product_code CHAR(4) NOT NULL
  , product_name VARCHAR(40) NOT NULL
  , product_desc VARCHAR(5000)
  , book_author INT
  , book_publisher INT
  , product_price SMALLMONEY NOT NULL CHECK (product_price >= 0)
  , CONSTRAINT CHK_author CHECK (
    CASE 
      WHEN product_code = 'BOOK' AND (book_author IS NULL OR book_publisher IS NULL) THEN 0
      ELSE 1
    END = 1
    )
);

CHK_author constraint will check if your product_code is BOOK, and if it is, then it will check whether book_author OR book_publisher are NULL values. If one of them is - it will restrict statement.

Here's a SQL Fiddle

Upvotes: 4

Related Questions