SQL Server 2017 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU24) (KB5001228) - 14.0.3391.2 (X64) 
	Apr 28 2021 10:32:18 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Linux (CentOS Linux 7 (Core))


CREATE TYPE ADDR FROM VARCHAR (40) NOT NULL
CREATE TABLE test
(
  f_name TEXT NOT NULL,
  l_name TEXT 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, 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


INSERT INTO test
  (f_name, l_name, addr1, addr2, addr3)
VALUES
  (
   'a',
   'b',
   (SELECT addr1 FROM test WHERE false),
   (SELECT addr1 FROM test WHERE false),
   (SELECT addr1 FROM test WHERE false)
  ) ;
Msg 4145 Level 15 State 1 Line 7
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Msg 4145 Level 15 State 1 Line 8
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Msg 4145 Level 15 State 1 Line 9
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

INSERT INTO test
  (f_name, l_name, addr1, addr2, addr3)
VALUES
  (
   'a',
   'b',
   (SELECT addr1 FROM test WHERE 1 = 0),
   (SELECT addr1 FROM test WHERE 1 = 0),
   (SELECT addr1 FROM test WHERE 1 = 0)
  ) ;
Msg 515 Level 16 State 2 Line 1
Cannot insert the value NULL into column 'addr1', table 'fiddle_e8329aec2274463aae538118f8fc1d65.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