SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TYPE ADDR FROM character varying (40) NOT NULL;
CREATE RULE ADDR_DOMAIN AS LEN(@ADDR) >= 5;
Msg 262 Level 14 State 18 Line 1
CREATE RULE permission denied in database 'fiddle_b820322033af4aebbc67c21716b1ac05'.

EXECUTE sys.sp_bindrule
    @rulename = N'ADDR_DOMAIN',
    @objname = N'ADDR',
    @futureonly = 'futureonly';
Msg 15017 Level 16 State 1 Line 104
The rule 'ADDR_DOMAIN' does not exist.

CREATE TABLE test
(
  f_name varchar(max) NOT NULL,
  l_name varchar(max) NOT NULL,
  
  addr1 ADDR,            -- << MUCH tidier than TEXT NOT NULL CHECK (LENGTH... 
  addr2 ADDR,
  addr3 ADDR 
);
INSERT INTO test
(f_name, l_name, addr1, addr2)
VALUES
('prenom_1', 'nom_1', 'My_addr_1_1', 'My_addr_1_2'),
('prenom_2', 'nom_2', 'My_addr_2_2', 'My_addr_2_2'),
('prenom_3', 'nom_3', 'My_addr_3_1', 'My_addr_3_2');
Msg 515 Level 16 State 2 Line 1
Cannot insert the value NULL into column 'addr3', table 'fiddle_b820322033af4aebbc67c21716b1ac05.dbo.test'; column does not allow nulls. INSERT fails.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

INSERT INTO test
(f_name, l_name, addr1, addr2, addr3)
VALUES
('prenom_1', 'nom_1', 'My_addr_1_1', 'My_addr_1_2', NULL),
('prenom_2', 'nom_2', 'My_addr_2_2', 'My_addr_2_2', NULL),
('prenom_3', 'nom_3', 'My_addr_3_1', 'My_addr_3_2', NULL);
Msg 515 Level 16 State 2 Line 1
Cannot insert the value NULL into column 'addr3', table 'fiddle_b820322033af4aebbc67c21716b1ac05.dbo.test'; column does not allow nulls. INSERT fails.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

SELECT * FROM test;


f_name
l_name
addr1
addr2
addr3


INSERT INTO test
(f_name, l_name, addr1, addr2, addr3)
VALUES
('prenom_1', 'nom_1', 'My_addr_1_1', 'My_addr_1_2', 'My_addr_1_3'),
('prenom_2', 'nom_2', 'My_addr_2_2', 'My_addr_2_2', 'My_addr_2_3'),
('prenom_3', 'nom_3', 'My_addr_3_1', 'My_addr_3_2', 'My_addr_3_3');
3 rows affected

SELECT * FROM test;


f_name
l_name
addr1
addr2
addr3


prenom_1
nom_1
My_addr_1_1
My_addr_1_2
My_addr_1_3

prenom_2
nom_2
My_addr_2_2
My_addr_2_2
My_addr_2_3

prenom_3
nom_3
My_addr_3_1
My_addr_3_2
My_addr_3_3


UPDATE test SET addr3 = NULL WHERE f_name = 'prenom_3';
Msg 515 Level 16 State 2 Line 1
Cannot insert the value NULL into column 'addr3', table 'fiddle_b820322033af4aebbc67c21716b1ac05.dbo.test'; column does not allow nulls. UPDATE fails.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

INSERT INTO test
  (f_name, l_name, addr1, addr2, addr3)
VALUES
  (
   'a',
   'b',
   (SELECT addr1 FROM test WHERE 0 = 1),
   (SELECT addr1 FROM test WHERE 0 = 1),
   (SELECT addr1 FROM test WHERE 0 = 1)
  ) ;
Msg 515 Level 16 State 2 Line 1
Cannot insert the value NULL into column 'addr1', table 'fiddle_b820322033af4aebbc67c21716b1ac05.dbo.test'; column does not allow nulls. INSERT fails.
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

SELECT * FROM test;


f_name
l_name
addr1
addr2
addr3


prenom_1
nom_1
My_addr_1_1
My_addr_1_2
My_addr_1_3

prenom_2
nom_2
My_addr_2_2
My_addr_2_2
My_addr_2_3

prenom_3
nom_3
My_addr_3_1
My_addr_3_2
My_addr_3_3
f_name	l_name	addr1	addr2	addr3
prenom_1	nom_1	My_addr_1_1	My_addr_1_2	My_addr_1_3
prenom_2	nom_2	My_addr_2_2	My_addr_2_2	My_addr_2_3
prenom_3	nom_3	My_addr_3_1	My_addr_3_2	My_addr_3_3