CODEWITHSUNDEEP
mysqlsqldatabaseuniquecreate-table
James
James

Reputation: 299

How to make 2 columns work together to be unique?

A customer, cus_id can own multiple houses. A house has a house_num by itself is not unique. However, together a cus_id and a house_num are unique. How can I make it so cus_id and house_num together are unique. So I can use INSERT IGNORE INTO and won't have to worry that I am going to insert a duplicate

CREATE TABLE house(
    house_id                    int
                                NOT NULL
                                AUTO_INCREMENT
                                PRIMARY KEY,

    cus_id                      int
                                NOT NULL,
                                FOREIGN KEY (cus_id) REFERENCES customers (cus_id),

    house_num                   int
                                NOT NULL
);

Example of a table

+----------+--------+-----------+
| house_id | cus_id | house_num |
+----------+--------+-----------+
|        1 |      3 |         4 |
|        2 |      3 |         5 |
|        3 |      8 |         4 |
|        4 |      9 |         2 |
+----------+--------+-----------+

However, this row cannot exist as it would be a duplicate of house_id 2

+----------+--------+-----------+
|        5 |      3 |         5 |
+----------+--------+-----------+

Upvotes: 0

Views: 32

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

Try adding a unique constraint on the combination of cus_id and house_num:

ALTER TABLE house ADD CONSTRAINT your_cnstr UNIQUE (cus_id, house_num);

Inside your CREATE statement you may try:

CREATE TABLE house (
    house_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cus_id int NOT NULL FOREIGN KEY (cus_id) REFERENCES customers (cus_id),
    house_num int NOT NULL,
    CONSTRAINT your_cnstr UNIQUE (cus_id, house_num)
);

Upvotes: 3

Related Questions