What is the difference between primary, unique and foreign key constraints, and indexes?

Question

What is the difference between primary, unique and foreign key constraints, and indexes?

I work on Oracle 10g and SQL Server 2008

Dakota

Answer 1

Primary Key and Unique Key are Entity integrity constraints

Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.

Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values. (In oracle one null is not equal to another null).

• KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any structure on your data so they are used only for speeding up queries.
• UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce structure on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data. Your database system may allow a UNIQUE index on columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (NULL is considered not equal to itself), though this is probably undesirable depending on your application.
• PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a way to uniquely identify any row in the table, so it shouldn't be used on any columns which allow NULL values. Your PRIMARY index should always be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.
• FULLTEXT indexes are different to all of the above, and their behaviour differs more between database systems. Unlike the above three, which are typically b-tree (allowing for selecting, sorting or ranges starting from left most column) or hash (allowing for selection starting from left most column), FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause.

see Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?

Diskin

NOTE: SQL Server unique constraints and indexes treat nulls as if they are equal so that "in which case two rows are allowed to be identical if they both contain a NULL value." is not true for SQL Server. – Opportuna

Answer 2

Primary Key: identify uniquely every row it can not be null. it can not be a duplicate.

Foreign Key: create relationship between two tables. can be null. can be a duplicate  

Margarettamargarette

Should all foreign keys be primary key of the other relation? If not then how null attribute would be identified uniquely. – Hickey

Answer 3

Here are some reference for you:

Primary & foreign key Constraint.

Primary Key: A primary key is a field or combination of fields that uniquely identify a record in a table, so that an individual record can be located without confusion.

Foreign Key: A foreign key (sometimes called a referencing key) is a key used to link two tables together. Typically you take the primary key field from one table and insert it into the other table where it becomes a foreign key (it remains a primary key in the original table).

Index, on the other hand, is an attribute that you can apply on some columns so that the data retrieval done on those columns can be speed up.

Zoniazoning

Answer 4

Key/index : A key is an aspect of a LOGICAL database design, an index is an aspect of a PHYSICAL database design. A key corresponds to an integrity constraint, an index is a technique of physically recording values that can be usefully applied when enforcing those constraints.

Primary/foreign : A "primary" key is a set of attributes whose values must form a combination that is unique in the entire table. There can be more than one such set (> 1 key), and the word "primary" is a remnant from the earlier days when the designer was then forced to choose one of those multiple keys as being "the most important/relevant one". The reason for this was primarily in combination with foreign keys :

Like a "primary" key, a "foreign" key is also a set of attributes. The values of these attributes must form a combination that is an existing primary key value in the referenced table. I don't know exactly how strict this rule still applies in SQL today. The terminology has remained anyway.

Unique : keyword used to indicate that an index cannot accept duplicate entries. Unique indexes are obviously an excellent means to enforce primary keys. To the extent that the word 'unique' is used in contexts of LOGICAL design, it is superfluous, sloppy, unnecessary and confusing. Keys (primary keys, that is) are unique by definition.

Leadwort

Answer 5

1)A primary key is a set of one or more attributes that uniquely identifies tuple within relation.

2)A foreign key is a set of attributes from a relation scheme which can be uniquely identify tuples fron another relation scheme.

Bainite

Tuple is a collection of data values which identifies the characteristics of an object. – Bainite

Answer 6

1. A primary key is a column or a set of columns that uniquely identify a row in a table. A primary key should be short, stable and simple. A foreign key is a column (or set of columns) in a second table whose value is required to match the value of the primary key in the original table. Usually a foreign key is in a table that is different from the table whose primary key is required to match. A table can have multiple foreign keys.
2. The primary key cannot accept null values. Foreign keys can accept multiple.
3. We can have only one primary key in a table. We can have more than one foreign key in a table.
4. By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index. Foreign keys do not automatically create an index, clustered or non-clustered. You can manually create an index on a foreign key.

Psychopathist

Answer 7

Primary key mainly prevent duplication and shows the uniqueness of columns Foreign key mainly shows relationship on two tables

Karimakarin