I have two tables:
First
------
id
name
Second
------
id
name
And another table connecting the first two:
Third
------
first_id
second_id
The third table is there only to resolve the M:N issue. Should it have its own ID?
It depends on role of the table "'third" (connection or join table). Lets assume for example, that this table express a relation between two others tables. Since it is all, there is no need for additional Id.
Every table (except for the rare conditions) should have a PRIMARY KEY , that is a value or a set of values that uniquely identify a row. See here for discussion why. IDENTITY is a property of a column in SQL Server which means that the column will be filled automatically with incrementing values.
SQL JOIN. A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Yes, it is possible for a table to have more than one column which can uniquely identify a row of data. A column that can uniquely identify a record of data is known as a "Candidate Key" .
If the table only contains the two foreign keys, there is no reason to have an additional key. You won't be using it in any query.
When you join the tables using the connection table, you are making a join against one foreign key at a time, not against both foreign keys at once, so there is no use for another key in the connection table. Example:
select t1.name, t2.name
from First t1
inner join Third t3 on t3.first_id = t1.id -- one foreign key
inner join Second t2 on t2.id = t3.second_id -- the other foreign key
Just make a primary key combining the two foreign keys.
PRIMARY KEY (first_id, second_id)
No, you normally don't need an id field on the table connecting the two. You can make the primary key for the connecting table (first_id, second_id).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With