Contents
Can foreign key refer to unique column?
A foreign key can refer to either a unique or a primary key of the parent table. If the column names of the key are not specified explicitly, the default is to refer to the column names of the primary key of the parent table.
Does foreign key have to be unique?
Any primary key must be unique and non-null. Therefore if the child table has a foreign key referencing the parent’s primary key, it must match a non-null, unique value, and therefore references exactly one row in the parent table. In this case you can’t make a child row that references multiple parent rows.
Which is a column in one table that must match the foreign key?
A foreign key is a column (or combination of columns) in a table whose values must match values of a column in some other table. FOREIGN KEY constraints enforce referential integrity, which essentially says that if column value A refers to column value B, then column value B must exist.
Can a foreign key point to a unique key?
A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. So in your case if you make AnotherID unique, it will be allowed.
Is foreign key should be primary key?
A foreign key is a reference to a unique identifier. Theoretically the reference should be to a primary key and not some other unique key. Unfortunately, in practice many databases have foreign key problems – about 50% of databases, we’ve found. Here are some common foreign key problems.
Is foreign key can be primary key?
Yes, it is legal to have a primary key being a foreign key. This is a rare construct, but it applies for: a 1:1 relation. The two tables cannot be merged in one because of different permissions and privileges only apply at table level (as of 2017, such a database would be odd).
Can a foreign key reference the same column as a primary key?
If the primary key has multiple columns, the foreign key must have the same number and order of columns. Therefore the foreign key references a unique row in the parent table; there can be no duplicates. Re your comment: If T.A is a primary key, then no you can’t have any duplicates.
Is it possible to have multiple keys on a foreign table?
No, it is not possible. When you define a foreign key constraint on a table, it means there is only one corresponding key on the foreign table. If multiples existed on the foreign table which one would be meant? Wikipedia has this definition on the Foreign key entry:
Can a foreign key reference a non unique index?
When this happens, it usually means that two foreign keys are being linked to each other. Often the table that would contain the key as a primary key isn’t even in the schema. Example: Two tables, COLLEGES and STUDENTS, both contain a column called ZIPCODE. We might discover that the relationship is many to many.
Is it possible to define a FOREIGN KEY constraint on a table?
No, it is not possible. When you define a foreign key constraint on a table, it means there is only one corresponding key on the foreign table.