SQL - Unique Key, Primary Key & Foreign Key -

what differences between unique key, primary key , foreign key respect concept of sql? how different each other.

a primary key , unique key constraints both similar , provide unique enforce uniqueness of column on defined.

primary key

• primary key cannot have null value.
• each table can have 1 primary key.
• by default, primary key clustered index , data in database table physically organized in sequence of clustered index.
• primary key can related table's foreign key.
• we can generated id automatically of auto increment field. primary key supports auto increment value.

unique key

• unique constraint may have null value.
• each table can have more 1 unique constraint.
• by default, unique key unique non-clustered index.
• unique constraint can not related table's foreign key.
• unique constraint doesn't supports auto increment value.

foreign key

• foreign key field in table primary key in table.
• foreign key can accept multiple null value.
• foreign key not automatically create index, clustered or non-clustered. can manually create index on foreign key.
• we can have more 1 foreign key in table.
• there actual advantages having foreign key supported clustered index, 1 per table. what's advantage? if selecting parent plus child records, want child records next each other. easy accomplish using clustered index.
• having null foreign key bad idea. in example below, record in [dbo].[child] referred "orphan record". think long , hard before doing this.