Yes. The foreign key can be composite meaning that two attributes can be a part of a primary key. Basically for a foreign key we basically refer to a particular tables primary key. I.e the parent table must have a primary key constraint or a unique constraint. And that constraint can be composite too which means on multiple attributes. Now generally if we have a table like
Products (Id, pname,pbrand,price ,quantity-avl)
Sales(Id,pname,pbrand,orderdate)
Or
Sales(id,product_id,orderdate)
Generally if we have a primary key constraint on multiple attributes we can have a unique constraint on an id attribute too. And it can be referred by a child table.
In the above case , if we have pname and pbrand in our sales table referreing a parent table that is products having pname and pbrand as primary key we can create foreign key constraint on the Id attribute which is also a unique constraint attribute.
Yes if we have a table referring to itself in the foreign key then that referred attribute must be unique. This type of structure us used in hierarchical data like managerid attribute refers employee ID in the same table which is the primary key.
We can have a foreign key attributes also as primary key only if we have a one to one relationship between the entities. If we have a many to one relationship then that child attribute will not be primary as multiple children may have same parent and it will not be a primary key in the child.
