MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.24


CREATE TABLE t
(
  x INT PRIMARY KEY,
  y VARCHAR (25),     -- imagine y & z to be first and last names and need to 
  z VARCHAR (50),     -- be unique
  
  CONSTRAINT y_z_uq UNIQUE (y, z)
);
INSERT INTO t (y, z)
VALUES
('Sean', 'Citizen');
Field 'x' doesn't have a default value

INSERT INTO t
VALUES
(NULL, 'Sean', 'Citizen');
Column 'x' cannot be null

INSERT INTO t 
VALUES 
(1, 'Sean', 'Citizen');
SELECT * FROM t;


x
y
z


1
Sean
Citizen


INSERT INTO t
VALUES
(2, 'Sean', NULL),
(3, 'Sean', NULL);
SELECT * FROM t;

-- you have to make y & z NOT NULL to ensure that this result doesn't happen.



x
y
z


2
Sean
null

3
Sean
null

1
Sean
Citizen


-- A better table design might be:

CREATE TABLE u
(
  x INT PRIMARY KEY,
  y VARCHAR (25) NOT NULL,     -- imagine y & z to be first and last names and need to 
  z VARCHAR (50) NOT NULL,     -- be unique
  
  
  -- not relevant to point at hand... just at a loose end at the minute! :-)
  
  CONSTRAINT y_z_uq UNIQUE (y, z),
  CONSTRAINT y_gt_2_ck            CHECK (LENGTH(y) > 2),
  CONSTRAINT z_gt_3_ck            CHECK (LENGTH(z) > 3),
  
  CONSTRAINT y_no_sp_beginning_ck CHECK (LEFT (y, 1) != ' '), -- y cannot begin
  CONSTRAINT y_no_sp_end_ck       CHECK (RIGHT(y, 1) != ' '), -- or end in space
  
  CONSTRAINT z_no_sp_beginning_ck CHECK (LEFT (z, 1) != ' '), -- neither can z 
  CONSTRAINT z_no_sp_end_ck       CHECK (RIGHT(z, 1) != ' '),
  
  CONSTRAINT y_no_double_space_ck CHECK (INSTR(y, '  ') = 0), -- no double space
  CONSTRAINT z_no_double_space_ck CHECK (INSTR(z, '  ') = 0)  -- within y or z
  
  -- could use a Regular Expression for name construction...
  
);