Can a Microsoft SQL table have more than one primary key?

Question

I was always curious, but I can not find a short answer. Any help?

Answer 1

It cannot have more than one primary key. . However, it can have more than one column in the primary key. It may also have more than one unique index.. Typically, the only index is the primary key, but you can have several unique indexes on the same table. At the top of my head I can’t come up with an example, but when I do this, I will add it.

EDIT . Perhaps this: in the United States, the Department of Motor Vehicles may have a table with two unique columns — a social security number and a driver’s license number. Both must be unique.

Answer 2

As noted by MJB, a table can have at most one Primary Key and must always have one (never zero). However, a table can have multiple candidate keys, one of which is designated as a primary key. In normalization theory, the primary key is not critical; candidate keys (the primary key is one of the candidate keys) are crucial in normalization theorems.

, , - Atomic Number, Symbol Element Name . , , , , - ( , , , ):

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL UNIQUE CONSTRAINT c1_elements
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE CONSTRAINT c2_elements,
    name            CHAR(20) NOT NULL UNIQUE CONSTRAINT c3_elements,
    atomic_weight   DECIMAL(8,4) NOT NULL,
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

( Informix Dynamic Server, Microsoft SQL Server SQL . , " ". , Atomic_Number .)

---

, . ISO/IEC 9075-2: 2003 (SQL/Foundation):

§11.7 <unique constraint definition>

.

<unique constraint definition> ::=
      <unique specification> <left paren> <unique column list> <right paren>
    | UNIQUE ( VALUE )
<unique specification> ::=
      UNIQUE
    | PRIMARY KEY
<unique column list> ::= <column name list>

• , <column name> <unique column list>, . 9.10 " ".
• T - , <table definition> <alter table statement>. TN - <table name> T.

• <unique column list> UCL,

  . <column name> <unique column list> T .
  . <unique column list> , T.
  . :
   . <unique specification> PRIMARY KEY, SC <search condition>:

          UNIQUE ( SELECT UCL FROM TN )
          AND
          ( UCL ) IS NOT NULL
  

  II. SC - <search condition>:

          UNIQUE ( SELECT UCL FROM TN )
  

• UNIQUE (VALUE), SC <search condition>:

      UNIQUE ( SELECT TN.* FROM TN )
  

• <unique specification> PRIMARY KEY, <column name> <unique column list>, NOT NULL , NOT NULL <column definition>.

• A <table definition> <unique constraint definition>, PRIMARY KEY.

• a <unique constraint definition>, PRIMARY KEY, <add table constraint definition>, , <table name>, <alter table statement>, , <unique constraint definition> .

.

• A <unique constraint definition> .
  254 - 10.8, "<constraint name definition> <constraint characteristics>", , .

• ,

    EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) )
  

  - True.

• S291 " ", SQL, UNIQUE (VALUE).
• T591, " ", SQL, UNIQUE, <column definition> , <column name> <unique column list> NOT NULL.

255 - 9.10, " " .

---

SQL! , T591 , , UNIQUE ( PRIMARY KEY). , , , UCL ( ) NULL:

      EXISTS ( SELECT * FROM TN WHERE NOT (
               UNIQUE ( SELECT UCL FROM TN ) ) )

, , UNIQUE .

---

§8.10 <unique predicate>

.

<unique predicate> ::= UNIQUE <table subquery>

• <table subquery> .
• <table subquery> . 9.10, " ", .

.

• T - .
• T , , <unique predicate> ; False.

1) F291, " ", SQL <unique predicate>.

193 - 9.10 " " .

Answer 3

, . ( 8 20, ) , . , , ( " ", "--" " " ).

Answer 4

, .

( PK, ). , , .