CODEWITHSUNDEEP
mysqlsqlsql-serverdatabaseperformance
Yony
Yony

Reputation: 105

is there any point in declaring that column as unique?

when i foreign reference a table, is there any point in declaring that column as unique?

I mean of course that column which I'm referencing to is unique.

Upvotes: 1

Views: 83

Answers (4)

Paul Wheeler
Paul Wheeler

Reputation: 20160

Unique constraints are generally created for the purpose of enforcing rules governing your data, as @yi_H said. However since unique constraints are equivalent to unique indexes, there are some pros and cons of creating them. An index on a field will require more time to perform inserts or updates to that field, since the index will need to be checked for duplicates and updated. An index will also increase the size of your table. On the pro side, if you perform queries based on the value of that field (i.e. WHERE myUniqueField = 5 or JOIN myTable ON myUniqueField = someOtherIndexedField) those queries will run much faster since they can do an index seek instead of a scan. These considerations only come into play when you have a large amount of data, or need to run a lot of queries quickly. So generally, if you have a field that should be unique, set it to be unique.

Upvotes: 1

Swift
Swift

Reputation: 13188

Yes, picture the following scenario:

Table 1's id field is your foreign key. It is a unique index.

Table 1:
________________________  
| id |  value | blah... |  
|----|--------|---------|  
| 0  |  val 1 | ....    |  
|----|--------|---------|  
| 1  |  val 2 | ....    |  
|----|--------|---------|  
 ...    ...       ...

Now there are two possibilities with table 2.

1) table_1_id is not unique

Table 2:
_____________________________
| id |  table_1_id | blah... |  
|----|-------------|---------|  
| 0  |      1      | ....    |  <------ Duplicates are okay!
|----|-------------|---------|  <--/
| 1  |      1      | ....    |  
|----|-------------|---------|  
 ...    ...       ...

2) table_1_id is unique

Table 2:
_____________________________
| id |  table_1_id | blah... |  
|----|-------------|---------|  
| 0  |      1      | ....    |  
|----|-------------|---------|  
| 1  |      n      | ....    |  <------- Cannot be 1, no duplicates
|----|-------------|---------|  
 ...    ...       ...

Upvotes: 3

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

multiple records can reference single row in the other table, and it's perfectly valid db design (1:M relation). So the question is, should be the field in the second table unique or not. And it's not clear from the question what are your performance considerations

Upvotes: 1

Karoly Horvath
Karoly Horvath

Reputation: 96266

You declare it as unique if you want it to be unique.

Upvotes: 1

Related Questions