0

I want to have my UNIQUE variables accept null values so I put the following code in my table:

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

I got this code from the following website: https://www.meadow.se/wordpress/unique-constraints-in-sql-server-does-not-allow-multiple-null-values/

However, I am unsure on where exactly to put it in my code. Currently I have it after the table has been created, but it still will not accept NULL values in the unique columns. Originally I had it inside, but I had errors. Here is my code:

drop table tblBus;

create table tblBus
(

    bus varchar (10) NOT NULL PRIMARY KEY,
    rb_id int UNIQUE,
    roadside_bottom varchar (20),   /*roadside_bottom Ads include King, Super King, Kong, Half Side*/
    rtb_id int UNIQUE,
    roadside_top_bottom varchar (20),   /*roadside_top_bottom Ads include Super Square, L-Side (Main), L-Side (Extended), King Kong */
    curb_id int UNIQUE,
    curbside varchar (20),
    rear_id int UNIQUE,
    rearside varchar (20),
    head_id int UNIQUE,
    headliner varchar(10),
    commissioned_date DATE NOT NULL,
    decommissioned_date DATE NOT NULL,

    CONSTRAINT FK_rb_id FOREIGN KEY (rb_id)
    REFERENCES tblRB_ID(rb_id2),
    CONSTRAINT FK_rtb_id FOREIGN KEY (rtb_id)
    REFERENCES tblRTB_ID(rtb_id2),
    CONSTRAINT FK_curb_id FOREIGN KEY (curb_id)
    REFERENCES tblCURB_ID(curb_id2),
    CONSTRAINT FK_rear_id FOREIGN KEY (rear_id)
    REFERENCES tblREAR_ID(rear_id2),
    CONSTRAINT FK_headliner_id FOREIGN KEY (head_id)
    REFERENCES tblHEADLINER_ID(head_id2),


    CONSTRAINT ad_pass CHECK
    (
        roadside_bottom IS NULL AND
        roadside_top_bottom = 'Full Wrap Side' AND
        curbside = 'Full Wrap Side' AND
        rearside = 'Full Wrap Back' AND
        headliner IS NULL OR
        
        roadside_top_bottom IS NULL AND
        roadside_bottom IN ('King', 'Super King','Kong','Half Side') AND
        curbside IN ('Queen', 'Empress') AND 
        headliner = 'Headliner' OR

        roadside_bottom IS NULL AND
        headliner is NULL AND
        roadside_top_bottom IN ('Super Square', 'L-Side (Main)', 'L-Side (Ext)', 'King Kong')
    ),
    

    CONSTRAINT date_pass CHECK
    (
        commissioned_date <= decommissioned_date
    )

);

**CREATE UNIQUE NONCLUSTERED INDEX idx_rb_id_notnull
ON tblBus(rb_id)
WHERE rb_id IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_rtb_id_notnull
ON tblBus(rtb_id)
WHERE rtb_id IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_curb_id_notnull
ON tblBus(curb_id)
WHERE curb_id IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_rear_id_notnull
ON tblBus(rear_id)
WHERE rear_id IS NOT NULL;
CREATE UNIQUE NONCLUSTERED INDEX idx_head_id_notnull
ON tblBus(head_id)
WHERE head_id IS NOT NULL;**

insert into tblBus (bus, commissioned_date, decommissioned_date)
values (1000, '2020-09-02', '2020-10-02');

insert into tblBus (bus, commissioned_date, decommissioned_date)
values (1001, '2020-09-02', '2020-10-02');

insert into tblBus (bus, commissioned_date, decommissioned_date)
values (1002, '2020-09-02', '2020-10-02');

insert into tblBus (bus, commissioned_date, decommissioned_date)
values (1003, '2020-09-02', '2020-10-02');

insert into tblBus (bus, commissioned_date, decommissioned_date)
values (1004, '2020-09-02', '2020-10-02');

SELECT * FROM tblBus;

Please help

Thanks

LongLouis
  • 41
  • 3
  • From the article you posted....https://stackoverflow.com/questions/767657/how-do-i-create-a-unique-constraint-that-also-allows-nulls – Sean Lange Sep 21 '21 at 22:27

0 Answers0