211:. Keys that are within their originating entity are unique within that entity. Keys that migrate to another entity may or may not be unique, depending on the design and how they are used in the other table. Foreign keys may be the primary key in another table; for example a PersonID may become the EmployeeID in the Employee table. In this case, the EmployeeID is both a foreign key and the unique primary key, meaning that the tables have a 1:1 relationship. In the case where the person entity contained the biological father ID, the father ID would not be expected to be unique because a father may have more than one child.
1245:
1255:
1265:
31:. All the candidate keys of a relation can uniquely identify the records of the relation, but only one of them is used as the primary key of the relation. The remaining candidate keys are called unique keys because they can uniquely identify a record in a relation. Unique keys can consist of multiple columns. Unique keys are also called
642:
Note that unlike the PRIMARY KEY constraint a UNIQUE constraint does not imply NOT NULL for the columns participating in the constraint. NOT NULL must be specified to make the column(s) a key. It is possible to put UNIQUE constraints on nullable columns but the SQL standard states that the constraint
178:
The key that is selected as the primary key. Only one key within an entity is selected to be the primary key. This is the key that is allowed to migrate to other entities to define the relationships that exist among the entities. When the data model is instantiated into a physical database, it is the
322:
or a set of columns in a single database table. No two distinct rows or data records in a database table can have the same data value (or combination of data values) in those candidate key columns since NULL values are not used. Depending on its design, a database table may have many candidate keys
59:
For example, in a table of employees, both employee number and login name are individually unique. The enforcement of a key constraint (i.e. a uniqueness constraint) in a table is also a data integrity feature of the database. The DBMS prevents updates that would cause duplicate key values and
646:
According to the SQL standard a unique constraint does not enforce uniqueness in the presence of nulls and can therefore contain several rows with identical combinations of nulls and non-null values — however not all RDBMS implement this feature according to the SQL standard.
42:
assigned to them in order to prevent duplicates (a duplicate entry is not valid in a unique column). Alternate keys may be used like the primary key when doing a single-table select or when filtering in a
162:
An artificial key made from data that is system assigned or generated when another candidate key exists. Surrogate keys are usually numeric ID values and often used for performance reasons.
330:
instances of tuples that could be stored in a table but it does imply a data integrity rule that duplicates should not be allowed in the database table. Some possible examples of keys are
60:
thereby ensures that tables always comply with the desired rules for uniqueness. Proper selection of keys when designing a database is therefore an important aspect of database integrity.
624:
A unique constraint can be defined on columns that allow nulls, in which case rows that include nulls may not actually be unique across the set of columns defined by the constraint.
326:
A key constraint applies to the set of tuples in a table at any given point in time. A key is not necessarily a unique identifier across the population of all
114:
A key made from more than one attribute joined together as a single key, such as part or whole name with a system generated number appended as often used for
82:
might be a single attribute key for an employee; a combination of flight number and date might be a key consisting of two attributes for a scheduled flight.
855:
713:
838:
730:"Constraints - SQL Database Reference Material - Learn sql, read an sql manual, follow an sql tutorial, or learn how to structure an SQL query!"
345:
only allow a foreign key constraint against a primary key but most systems will allow a foreign key constraint to reference any key of a table.
187:
A non-primary key that can be used to identify only one row in a table. Alternate keys may be used like a primary key in a single-table select.
850:
315:
in the table 'Book'. The
Foreign Key serves as the link, and therefore the connection, between the two related tables in this sample database.
318:
In a relational database, a candidate key uniquely identifies each row of data values in a database table. A candidate key comprises a single
214:
Here is an example of a primary key becoming a foreign key on a related table. ID migrates from the Author table to the Book table.
55:
Keys provide the means for database users and application software to identify, access and update information in a database table.
643:
does not guarantee uniqueness of nullable columns (uniqueness is not enforced for rows where any of the columns contains a null).
20:
1289:
1248:
148:
A key made from data that exists outside the current database. In other words, the data is not system generated, such as a
921:
810:
335:
782:
696:
1268:
974:
729:
179:
key that the system uses the most when accessing the table, or joining the tables together when selecting data.
1225:
872:
1164:
710:
662:
1159:
1190:
909:
1113:
1103:
879:
656:
1200:
933:
35:. Unique keys are an alternative to the primary key of the relation. In SQL, the unique keys have a
128:
A key made from at least two attributes or simple keys, only simple keys exist in a compound key.
1149:
803:
1294:
1230:
1185:
862:
331:
149:
79:
1205:
959:
39:
1258:
1195:
1077:
1047:
916:
867:
311:
Here ID serves as the primary key in the table 'Author', but also as AuthorID serves as a
8:
1215:
1108:
1093:
1020:
845:
63:
A relational database table may have one or more available unique keys (formally called
1210:
1154:
1123:
1072:
964:
904:
796:
635:
754:
1030:
884:
692:
685:
319:
1220:
1067:
1057:
1025:
585:
1128:
1098:
1052:
833:
717:
611:
1180:
1118:
1062:
1035:
928:
889:
86:
788:
1283:
999:
984:
157:
72:
64:
32:
28:
138:
Like a compound key, but the individual attributes need not be simple keys.
665:(POID), a unique identifier of a record on a table, used as the primary key
133:
123:
989:
969:
600:
allow null (a primary key cannot be defined on columns that allow nulls).
312:
192:
143:
68:
1133:
1042:
979:
323:
but at most one candidate key may be distinguished as the primary key.
994:
949:
819:
208:
341:
In principle any key may be referenced by foreign keys. Some SQL
203:
At the most basic definition, "a key is a unique identifier", so
586:
Differences between primary key constraint and unique constraint
899:
115:
78:
Any key may consist of one or more attributes. For example, a
894:
631:
607:
47:
clause, but are not typically used to join multiple tables.
954:
342:
659:(GUID), also known as universally unique identifier (UUID)
785:: An overview of the different types of keys in an RDBMS
67:). One of those keys per table may be designated the
338:, vehicle registration numbers or user login names.
684:
603:Each table cannot have more than one primary key.
1281:
627:Each table can have multiple unique constraints.
818:
804:
755:"Comparison of different SQL implementations"
417:Likewise, keys can be defined as part of the
57:There may be several keys in any given table.
687:Systems Analysis and Design, Second Edition
197:A key that has migrated to another entity.
811:
797:
783:Relation Database terms of reference, Keys
85:There are several types of keys used in
348:
170:A key that may become the primary key.
1282:
21:relational database management systems
792:
16:Concept in relational database design
682:
106:A key made from only one attribute.
1264:
13:
14:
1306:
776:
1263:
1253:
1244:
1243:
634:a unique constraint generates a
1254:
353:The definition of keys in SQL:
747:
722:
704:
676:
152:imported from another system.
1:
669:
663:Persistent object identifier
7:
1290:Database management systems
820:Database management systems
711:Summary of ANSI/ISO/IEC SQL
650:
10:
1311:
1226:Object–relational database
691:, Richard D. Irwin, Inc.,
657:Globally unique identifier
610:a primary key generates a
50:
1239:
1201:Federated database system
1173:
1142:
1086:
1013:
942:
934:Blockchain-based database
826:
508:
423:
355:
216:
71:; other keys are called
716:April 25, 2012, at the
332:Social Security Numbers
1231:Transaction processing
1186:Database normalization
1129:Query rewriting system
591:Primary key constraint
150:social security number
80:Social Security Number
1206:Referential integrity
89:and implementations.
1196:Distributed database
683:Awad, Elias (1985),
349:Defining keys in SQL
1216:Relational calculus
1094:Concurrency control
1211:Relational algebra
1155:Query optimization
960:Armstrong's axioms
636:nonclustered index
1277:
1276:
885:Wide-column store
880:Document-oriented
619:Unique constraint
201:
200:
87:database modeling
1302:
1267:
1266:
1257:
1256:
1247:
1246:
1221:Relational model
1191:Database storage
1068:Stored procedure
813:
806:
799:
790:
789:
770:
769:
767:
765:
751:
745:
744:
742:
740:
726:
720:
708:
702:
701:
690:
680:
581:
578:
575:
572:
569:
566:
563:
560:
557:
554:
551:
548:
545:
542:
539:
536:
533:
530:
527:
524:
521:
518:
515:
512:
505:
502:
499:
496:
493:
490:
487:
484:
481:
478:
475:
472:
469:
466:
463:
460:
457:
454:
451:
448:
445:
442:
439:
436:
433:
430:
427:
420:
413:
410:
407:
404:
401:
398:
395:
392:
389:
386:
383:
380:
377:
374:
371:
368:
365:
362:
359:
307:
304:
301:
298:
295:
292:
289:
286:
283:
280:
277:
274:
271:
268:
265:
262:
259:
256:
253:
250:
247:
244:
241:
238:
235:
232:
229:
226:
223:
220:
92:
91:
38:
1310:
1309:
1305:
1304:
1303:
1301:
1300:
1299:
1280:
1279:
1278:
1273:
1235:
1181:Database models
1169:
1138:
1124:Query optimizer
1099:Data dictionary
1082:
1053:Transaction log
1009:
965:Codd's 12 rules
938:
868:Column-oriented
834:Object-oriented
822:
817:
779:
774:
773:
763:
761:
759:troels.arvin.dk
753:
752:
748:
738:
736:
728:
727:
723:
718:Wayback Machine
709:
705:
699:
681:
677:
672:
653:
612:clustered index
588:
583:
582:
579:
576:
573:
570:
567:
564:
561:
558:
555:
552:
549:
546:
543:
540:
537:
534:
531:
528:
525:
522:
519:
516:
513:
510:
507:
506:
503:
500:
497:
494:
491:
488:
485:
482:
479:
476:
473:
470:
467:
464:
461:
458:
455:
452:
449:
446:
443:
440:
437:
434:
431:
428:
425:
421:SQL statement.
418:
415:
414:
411:
408:
405:
402:
399:
396:
393:
390:
387:
384:
381:
378:
375:
372:
369:
366:
363:
360:
357:
351:
309:
308:
305:
302:
299:
296:
293:
290:
287:
284:
281:
278:
275:
272:
269:
266:
263:
260:
257:
254:
251:
248:
245:
242:
239:
236:
233:
230:
227:
224:
221:
218:
53:
36:
17:
12:
11:
5:
1308:
1298:
1297:
1292:
1275:
1274:
1272:
1271:
1261:
1251:
1240:
1237:
1236:
1234:
1233:
1228:
1223:
1218:
1213:
1208:
1203:
1198:
1193:
1188:
1183:
1177:
1175:
1174:Related topics
1171:
1170:
1168:
1167:
1162:
1157:
1152:
1150:Administration
1146:
1144:
1140:
1139:
1137:
1136:
1131:
1126:
1121:
1119:Query language
1116:
1111:
1106:
1101:
1096:
1090:
1088:
1084:
1083:
1081:
1080:
1075:
1070:
1065:
1060:
1055:
1050:
1045:
1040:
1039:
1038:
1033:
1028:
1017:
1015:
1011:
1010:
1008:
1007:
1002:
997:
992:
987:
982:
977:
972:
967:
962:
957:
952:
946:
944:
940:
939:
937:
936:
931:
926:
925:
924:
914:
913:
912:
902:
897:
892:
887:
882:
877:
876:
875:
865:
860:
859:
858:
853:
843:
842:
841:
830:
828:
824:
823:
816:
815:
808:
801:
793:
787:
786:
778:
777:External links
775:
772:
771:
746:
721:
703:
697:
674:
673:
671:
668:
667:
666:
660:
652:
649:
640:
639:
628:
625:
616:
615:
604:
601:
596:A primary key
587:
584:
509:
424:
356:
350:
347:
217:
199:
198:
195:
189:
188:
185:
181:
180:
176:
172:
171:
168:
164:
163:
160:
154:
153:
146:
140:
139:
136:
130:
129:
126:
120:
119:
112:
108:
107:
104:
100:
99:
96:
73:alternate keys
65:candidate keys
52:
49:
33:alternate keys
15:
9:
6:
4:
3:
2:
1307:
1296:
1295:Data modeling
1293:
1291:
1288:
1287:
1285:
1270:
1262:
1260:
1252:
1250:
1242:
1241:
1238:
1232:
1229:
1227:
1224:
1222:
1219:
1217:
1214:
1212:
1209:
1207:
1204:
1202:
1199:
1197:
1194:
1192:
1189:
1187:
1184:
1182:
1179:
1178:
1176:
1172:
1166:
1163:
1161:
1158:
1156:
1153:
1151:
1148:
1147:
1145:
1141:
1135:
1132:
1130:
1127:
1125:
1122:
1120:
1117:
1115:
1112:
1110:
1107:
1105:
1102:
1100:
1097:
1095:
1092:
1091:
1089:
1085:
1079:
1076:
1074:
1071:
1069:
1066:
1064:
1061:
1059:
1056:
1054:
1051:
1049:
1046:
1044:
1041:
1037:
1034:
1032:
1029:
1027:
1024:
1023:
1022:
1019:
1018:
1016:
1012:
1006:
1003:
1001:
1000:Surrogate key
998:
996:
993:
991:
988:
986:
985:Candidate key
983:
981:
978:
976:
973:
971:
968:
966:
963:
961:
958:
956:
953:
951:
948:
947:
945:
941:
935:
932:
930:
927:
923:
920:
919:
918:
915:
911:
908:
907:
906:
903:
901:
898:
896:
893:
891:
888:
886:
883:
881:
878:
874:
871:
870:
869:
866:
864:
861:
857:
854:
852:
849:
848:
847:
844:
840:
837:
836:
835:
832:
831:
829:
825:
821:
814:
809:
807:
802:
800:
795:
794:
791:
784:
781:
780:
760:
756:
750:
735:
731:
725:
719:
715:
712:
707:
700:
698:0-256-02824-9
694:
689:
688:
679:
675:
664:
661:
658:
655:
654:
648:
644:
637:
633:
629:
626:
623:
622:
621:
620:
613:
609:
605:
602:
599:
595:
594:
593:
592:
422:
354:
346:
344:
339:
337:
333:
329:
324:
321:
316:
314:
215:
212:
210:
206:
196:
194:
191:
190:
186:
183:
182:
177:
174:
173:
169:
166:
165:
161:
159:
156:
155:
151:
147:
145:
142:
141:
137:
135:
132:
131:
127:
125:
122:
121:
117:
113:
111:Concatenated
110:
109:
105:
102:
101:
97:
94:
93:
90:
88:
83:
81:
76:
74:
70:
66:
61:
58:
48:
46:
41:
34:
30:
29:candidate key
26:
22:
1004:
762:. Retrieved
758:
749:
737:. Retrieved
733:
724:
706:
686:
678:
645:
641:
618:
617:
597:
590:
589:
419:CREATE TABLE
416:
352:
340:
327:
325:
317:
310:
213:
204:
202:
84:
77:
62:
56:
54:
44:
24:
18:
1269:WikiProject
1160:Replication
1048:Transaction
990:Foreign key
970:CAP theorem
917:Multi-model
734:www.sql.org
638:by default.
614:by default.
313:Foreign Key
118:addresses.
98:Definition
69:primary key
1284:Categories
1134:Query plan
1087:Components
1005:Unique key
922:comparison
856:comparison
846:Relational
839:comparison
670:References
517:table_name
486:key_unique
483:CONSTRAINT
432:table_name
370:identifier
184:Alternate
167:Candidate
40:constraint
25:unique key
1143:Functions
1078:Partition
905:In-memory
863:Key–value
764:16 August
739:16 August
541:CHARACTER
450:CHARACTER
297:Publisher
207:key is a
158:Surrogate
134:Composite
95:Key Name
1249:Category
1165:Sharding
1021:Relation
995:Superkey
950:Database
943:Concepts
714:Archived
651:See also
630:On some
606:On some
562:SMALLINT
468:SMALLINT
328:possible
285:AuthorID
209:pleonasm
175:Primary
124:Compound
1259:Outline
1058:Trigger
1014:Objects
559:key_col
544:VARYING
529:PRIMARY
495:key_col
465:key_col
453:VARYING
382:PRIMARY
249:Address
193:Foreign
144:Natural
103:Simple
51:Summary
1073:Cursor
1031:column
900:NewSQL
695:
598:cannot
571:UNIQUE
523:id_col
511:CREATE
489:UNIQUE
438:id_col
426:CREATE
403:column
391:UNIQUE
320:column
267:Schema
231:Author
225:Schema
219:Author
205:unique
116:E-mail
37:UNIQUE
1063:Index
1026:table
929:Cloud
895:NoSQL
890:Graph
827:Types
632:RDBMS
608:RDBMS
514:TABLE
429:TABLE
367:table
361:TABLE
358:ALTER
343:DBMSs
336:ISBNs
303:Price
291:Title
264:Table
222:Table
45:where
27:is a
1114:ODBC
1104:JDBC
1043:View
980:Null
975:CRUD
955:ACID
910:list
873:list
851:list
766:2018
741:2018
693:ISBN
568:NULL
538:col2
474:NULL
447:col2
409:>
406:name
400:<
373:>
364:<
279:ISBN
273:Book
261:Book
255:Born
243:Name
23:, a
1109:XQJ
1036:row
577:...
565:NOT
556:...
532:KEY
526:INT
501:...
480:...
471:NOT
441:INT
385:KEY
376:ADD
19:In
1286::
757:.
732:.
553:),
550:20
498:),
462:),
459:20
334:,
237:ID
75:.
812:e
805:t
798:v
768:.
743:.
580:)
574:,
547:(
535:,
520:(
504:)
492:(
477:,
456:(
444:,
435:(
412:)
397:(
394:}
388:|
379:{
306:)
300:,
294:,
288:,
282:,
276:(
270::
258:)
252:,
246:,
240:,
234:(
228::
Text is available under the Creative Commons Attribution-ShareAlike License. Additional terms may apply.