Topics

Keys in SQL Server

Rumman Ansari 2020-03-08 Student SQL SERVER > Keys 721 Share

Example:

Code: Primary Key

<span class="pln">


</span><span class="pun">--</span><span class="pln"> </span><span class="typ">All</span><span class="pln"> </span><span class="typ">Key</span><span class="pln"> </span><span class="typ">Examples</span><span class="pln"> 

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Example</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Primary</span><span class="pln"> </span><span class="typ">Key</span><span class="pln">

CREATE TABLE SAMPLE_TABLE1 </span><span class="pun">(</span><span class="pln">
	COL1 integer</span><span class="pun">,</span><span class="pln">
	COL2 nvarchar</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln">
	COL3 nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
	PRIMARY KEY </span><span class="pun">(</span><span class="pln">COL1</span><span class="pun">)</span><span class="pln">
</span><span class="pun">);</span><span class="pln">


</span><span class="str">&lt;h4&gt;</span><span class="pln"> </span><span class="typ">Code</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Insert</span><span class="pln"> </span><span class="typ">Data</span><span class="pln"> </span><span class="pun">&lt;/</span><span class="pln">h4</span><span class="pun">&gt;</span><span class="pln">
</span><span class="pun">&lt;</span><span class="pln">pre </span><span class="kwd">class</span><span class="pun">=</span><span class="str">"prettyprint"</span><span class="pun">&gt;</span><span class="pln">
</span><span class="str">&lt;xmp&gt;</span><span class="pln">

INSERT INTO SAMPLE_TABLE1 VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">)</span><span class="pln">


INSERT INTO SAMPLE_TABLE1 VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">)</span><span class="pln">
 </span><span class="pun">--</span><span class="pln"> </span><span class="typ">This</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> possible </span><span class="kwd">for</span><span class="pln"> primary key </span><span class="pun">(</span><span class="typ">The</span><span class="pln"> duplicate key </span><span class="kwd">value</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="pun">(</span><span class="lit">1</span><span class="pun">))</span><span class="pln">
</span>

Code: Composite Key

<span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Composite</span><span class="pln"> </span><span class="typ">Key</span><span class="pln">

CREATE TABLE SAMPLE_TABLE </span><span class="pun">(</span><span class="pln">
	COL1 integer</span><span class="pun">,</span><span class="pln">
	COL2 nvarchar</span><span class="pun">(</span><span class="lit">30</span><span class="pun">),</span><span class="pln">
	COL3 nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln">
	PRIMARY KEY </span><span class="pun">(</span><span class="pln">COL1</span><span class="pun">,</span><span class="pln"> COL2</span><span class="pun">)</span><span class="pln">
</span><span class="pun">);</span><span class="pln">

INSERT INTO SAMPLE_TABLE VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">)</span><span class="pln">

INSERT INTO SAMPLE_TABLE VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">)</span><span class="pln">
 </span><span class="pun">--</span><span class="pln"> </span><span class="kwd">this</span><span class="pln"> can</span><span class="str">'t be inserted (The duplicate key value is (1, text))


SELECT * FROM SAMPLE_TABLE
</span>

Code: Unique Key

<span class="pln">

</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Unique</span><span class="pln"> key 

CREATE TABLE SAMPLE_TABLE3 </span><span class="pun">(</span><span class="pln">
    COL1 </span><span class="kwd">int</span><span class="pln"> NOT NULL UNIQUE</span><span class="pun">,</span><span class="pln">
    COL2 varchar</span><span class="pun">(</span><span class="lit">255</span><span class="pun">)</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln">
    COL3 varchar</span><span class="pun">(</span><span class="lit">255</span><span class="pun">),</span><span class="pln"> 
</span><span class="pun">);</span><span class="pln">

INSERT INTO SAMPLE_TABLE3 VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">)</span><span class="pln">

INSERT INTO SAMPLE_TABLE3 VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">)</span><span class="pln">
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">This</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="kwd">not</span><span class="pln"> possible </span><span class="pun">(</span><span class="typ">The</span><span class="pln"> duplicate key </span><span class="kwd">value</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="pun">(</span><span class="lit">1</span><span class="pun">))</span><span class="pln">


</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Point</span><span class="pln"> </span><span class="lit">1</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Can</span><span class="pln"> be more than one unique key </span><span class="kwd">in</span><span class="pln"> one table
CREATE TABLE SAMPLE_TABLE4 </span><span class="pun">(</span><span class="pln">
    COL1 </span><span class="kwd">int</span><span class="pln"> UNIQUE</span><span class="pun">,</span><span class="pln">
    COL2 varchar</span><span class="pun">(</span><span class="lit">255</span><span class="pun">)</span><span class="pln"> UNIQUE</span><span class="pun">,</span><span class="pln">
    COL3 varchar</span><span class="pun">(</span><span class="lit">255</span><span class="pun">)</span><span class="pln"> UNIQUE
</span><span class="pun">);</span><span class="pln">

INSERT INTO SAMPLE_TABLE4 VALUES
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">),</span><span class="pln">
</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc2'</span><span class="pun">)</span><span class="pln">

INSERT INTO SAMPLE_TABLE4 VALUES
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Not</span><span class="pln"> </span><span class="typ">Possible</span><span class="pln"> </span><span class="pun">(</span><span class="typ">The</span><span class="pln"> duplicate key </span><span class="kwd">value</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="pun">(</span><span class="pln">abc</span><span class="pun">))</span><span class="pln">
</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc3'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Not</span><span class="pln"> </span><span class="typ">Possible</span><span class="pln"> </span><span class="pun">(</span><span class="typ">The</span><span class="pln"> duplicate key </span><span class="kwd">value</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="pun">(</span><span class="pln">text</span><span class="pun">))</span><span class="pln">
</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'text3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'abc3'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Not</span><span class="pln"> possible </span><span class="pun">(</span><span class="typ">The</span><span class="pln"> duplicate key </span><span class="kwd">value</span><span class="pln"> </span><span class="kwd">is</span><span class="pln"> </span><span class="pun">(</span><span class="lit">1</span><span class="pun">))</span><span class="pln">

SELECT </span><span class="pun">*</span><span class="pln"> FROM SAMPLE_TABLE4
</span><span class="pun">--</span><span class="pln"> </span><span class="typ">Point</span><span class="pln"> </span><span class="lit">2</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Unique</span><span class="pln"> key can have NULL values
INSERT INTO SAMPLE_TABLE4 VALUES
</span><span class="pun">(</span><span class="pln">NULL</span><span class="pun">,</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> NULL</span><span class="pun">)</span><span class="pln">

</span>

Share this Post: