What's the distinction between primary, unique and foreign key constraints, and indexes?

Sometimes on Oracle 10g and SQL Server 2008

Primary key and different are Entity integrity constraints

Primary key enables each row inside a table to become distinctively recognized and guarantees that no duplicate rows exist with no null values are joined.

Unique key constraint can be used to avoid the duplication of key values inside the rows of the table and permit null values. (In oracle one null isn't comparable to another null).

  • KEY or INDEX describes an ordinary non-unique index. Non-distinct values for that index are permitted, therefore the index could have rows with identical values in most posts from the index. These indexes don't enforce any structure in your data so they are utilised just for accelerating queries.
  • UNIQUE describes a catalog where all rows from the index should be unique. That's, exactly the same row might not have identical non-NULL values for those posts within this index as the second row. In addition to being accustomed to accelerate queries, UNIQUE indexes may be used to enforce structure on data, since the database system doesn't allow this distinct values rule to become damaged when placing or upgrading data. Your database system may allow a distinctive index on posts which permit NULL values, by which situation two rows are permitted to become identical when they both have a NULL value (NULL is recognized as not comparable to itself), though this really is most likely undesirable based on the application.
  • PRIMARY functions the same as a distinctive index, except that it's always named 'PRIMARY', there might be just one on the table (there ought to always be one though some database systems don't enforce this). A Principal index is supposed in an effort to distinctively identify any row within the table, therefore it should not be utilized on any posts which permit NULL values. Most Of Your index ought to always be around the littlest quantity of posts which are sufficient to distinctively identify a row. Frequently, case one column that contains a distinctive auto-incremented number, but when there's other things that may distinctively identify a row, for example "countrycode" in a listing of nations, you should use that rather.
  • FULLTEXT indexes will vary to the suggestions above, as well as their behavior differs more between database systems. Unlike the above mentioned three, that are typically b-tree (permitting for choosing, sorting or ranges beginning from left most column) or hash (permitting for selection beginning from left most column), FULLTEXT indexes are just helpful for full text searches completed with the MATCH() / AGAINST() clause.

see http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-mysql

Here are a few reference for you personally:

Primary & foreign key Constraint.

Primary Key: A principal secret is a area or mixture of fields that distinctively identify an archive inside a table, to ensure that a person record could be situated without confusion.

Foreign Key: An overseas key (sometimes known as a referencing key) is really a key accustomed to link two tables together. Typically you are taking the main key area in one table and place it in to the other table where it might be an overseas key (it remains a principal type in the initial table).

Index, however, is definitely an attribute that you could apply on some posts to ensure that the information retrieval done on individuals posts could be accelerate.

primary key identify distinctively every row it cannot be null. it cannot b duplicate.. foreign key: create relationship between two tables. could be null. could be duplicate