[sql-server] What is the difference between primary, unique and foreign key constraints, and indexes?

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

I work on Oracle 10g and SQL Server 2008

This question is related to sql-server database oracle constraints indexing

The answer is


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.


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.


  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.

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


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  


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.


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to constraints

How to trap on UIViewAlertForUnsatisfiableConstraints? trying to animate a constraint in swift Remove all constraints affecting a UIView "Insert if not exists" statement in SQLite Unique Key constraints for multiple columns in Entity Framework SQL Server 2008- Get table constraints How to remove constraints from my MySQL table? Creating layout constraints programmatically Display names of all constraints for a table in Oracle SQL Add primary key to existing table

Examples related to indexing

numpy array TypeError: only integer scalar arrays can be converted to a scalar index How to print a specific row of a pandas DataFrame? What does 'index 0 is out of bounds for axis 0 with size 0' mean? How does String.Index work in Swift Pandas KeyError: value not in index Update row values where certain condition is met in pandas Pandas split DataFrame by column value Rebuild all indexes in a Database How are iloc and loc different? pandas loc vs. iloc vs. at vs. iat?