Is there a way to prevent duplicate entries in an SQL DB that involves multiple columns?

Question

I have a database table of addresses, each address is set up so that it is split into its component parts, such as:

Address ID(PK, AI) 
Customer ID
Street Number
Street Name
City
etc.

I am wondering what the best way would be to add data so as not to have a duplicate combination of these values?

MY customers may have multiple addresses, but I want each combination to be unique.

Is there some way to throw an error when an entire row equals another row?

Thanks

you could create a unique index over all your columns, see here https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql — hbrgnr, Jan 31 '21 at 20:26

score -1 · Answer 1 · answered Jan 31 '21 at 20:22

This is a many to many relation:

One way is to split into 3 tables:

Customers:{
    Customer ID (PK)
}

Address:{
   Address ID(PK)
   Street Number
   Street Name
   City
}

CustomerAddress{
    CustomerID (FK)
    AddressID (FK)

    (AddressId, CustomerID) = PK
}

(AddressId, CustomerID) = PK will ensure that the combination of (customer <-> address) is unique

Is there a way to prevent duplicate entries in an SQL DB that involves multiple columns?

1 Answers1