Keys in SQL Server

Example:

Code: Primary Key

-- All Key Examples 

-- Example: Primary Key

CREATE TABLE SAMPLE_TABLE1 (
	COL1 integer,
	COL2 nvarchar(30),
	COL3 nvarchar(50),
	PRIMARY KEY (COL1)
);


<h4> Code: Insert Data </h4>
<pre class="prettyprint">
<xmp>

INSERT INTO SAMPLE_TABLE1 VALUES
(1, 'text', 'abc'),
(2, 'text', 'abc')


INSERT INTO SAMPLE_TABLE1 VALUES
(1, 'text', 'abc')
 -- This is not possible for primary key (The duplicate key value is (1))

Code: Composite Key

-- Composite Key

CREATE TABLE SAMPLE_TABLE (
	COL1 integer,
	COL2 nvarchar(30),
	COL3 nvarchar(50),
	PRIMARY KEY (COL1, COL2)
);

INSERT INTO SAMPLE_TABLE VALUES
(1, 'text', 'abc'),
(1, 'text1', 'abc')

INSERT INTO SAMPLE_TABLE VALUES
(1, 'text', 'abc')
 -- this can't be inserted (The duplicate key value is (1, text))


SELECT * FROM SAMPLE_TABLE

Code: Unique Key

-- Unique key 

CREATE TABLE SAMPLE_TABLE3 (
    COL1 int NOT NULL UNIQUE,
    COL2 varchar(255) NOT NULL,
    COL3 varchar(255), 
);

INSERT INTO SAMPLE_TABLE3 VALUES
(1, 'text', 'abc'),
(2, 'text1', 'abc')

INSERT INTO SAMPLE_TABLE3 VALUES
(1, 'text', 'abc')
-- This is not possible (The duplicate key value is (1))


-- Point 1: Can be more than one unique key in one table
CREATE TABLE SAMPLE_TABLE4 (
    COL1 int UNIQUE,
    COL2 varchar(255) UNIQUE,
    COL3 varchar(255) UNIQUE
);

INSERT INTO SAMPLE_TABLE4 VALUES
(1, 'text', 'abc'),
(2, 'text1', 'abc2')

INSERT INTO SAMPLE_TABLE4 VALUES
(3, 'text3', 'abc') -- Not Possible (The duplicate key value is (abc))
(3, 'text', 'abc3') -- Not Possible (The duplicate key value is (text))
(1, 'text3', 'abc3') -- Not possible (The duplicate key value is (1))

SELECT * FROM SAMPLE_TABLE4
-- Point 2: Unique key can have NULL values
INSERT INTO SAMPLE_TABLE4 VALUES
(NULL, NULL, NULL)