I have a table with a UNIUE constraint on 2 fields
CREATE TABLE buildings (
building_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
number SMALLINT UNSIGNED,
notes TEXT DEFAULT 'None',
created_by VARCHAR(50) DEFAULT 'firstname.lastname@example.org',
created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_date TIMESTAMP NULL,
operational_status BOOLEAN DEFAULT 1,
PRIMARY KEY ( building_id )
Is there a way to show an error oonly if a duplicate entry (name, number) is INSERTed
You dont have a unique restraint on two fields. You have a unique restraint on the tuple of two fields.
IE you’ve already got what youre asking for.
you don’t have to
the database will generate an error message for you
A unique constraint on two fields basically means that the combination of them should be unique. In this case, you can repeat the name “John Doe” multiple times and also the Number “1” but never the combination (i.e. name=John Doe, Number=1). You can definitely repeat “John Doe” multiple times with the number 2, 3, 4, etc.
This kind of constraint is helpful in case of Brand+Product, for example in an ecommerce system. You can have “Laptop” as a product name repeated multiple times but the brand could be “Dell”, “Lenovo”, etc. The combination of two (Dell+Laptop) can never be duplicated twice.
On the other hand, if you want the name to be absolutely unique for some reason and also the number (there can’t be two "John Doe"s in your department!), you can have two separate unique constraints as follows: