Creating table with multiple check constraints on a column

Question

Im trying to create a new 'machines' table with a constraint on the 'network' column; it can't be null and must be any one of three different strings. Im new to SQL and its syntax and am using mysql 5.7

DROP TABLE IF EXISTS `machines`;

CREATE TABLE `machines` (
    `id` int(11) NOT NULL,
    `hostname` varchar(255),
    `ip_address` varchar(15),
    `network` varchar(255) NOT NULL,
    CONSTRAINT CHK_network CHECK (network = 'INTERNAL' OR 'EXTERNAL' OR 'OTHER'),
    PRIMARY KEY (`id`)
    );

INSERT INTO `machines`

VALUES
    (1, 'host1', '123.123.123.1', 'EXTERNAL'),
    (2, 'host2', '192.168.0.1', 'EXTERNAL' ),
    (3, 'host3', '192.168.0.2', '       ' );

I was hoping that whitespace wouldnt be entered into the row three but it is.

score 0 · Answer 1 · answered Jun 21 '19 at 13:38

0

Define your constraint like this:

CONSTRAINT CHK_network CHECK (network IN ('INTERNAL', 'EXTERNAL', 'OTHER'))

answered Jun 21 '19 at 13:38

Robert Kock

5,795
1
12
20

score 0 · Accepted Answer · answered Jun 21 '19 at 13:38

You can change column network to ENUM

CREATE TABLE `machines` (
    `id` int(11) NOT NULL,
    `hostname` varchar(255),
    `ip_address` varchar(15),
    `network` enum('INTERNAL','EXTERNAL','OTHER'),
    PRIMARY KEY (`id`)
);

so insert give you a

Data truncated for column 'network' at row 3

check the manual Here

Creating table with multiple check constraints on a column

2 Answers2