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