MS SQL Server Keys
MS SQL Server PRIMARY KEY
- PRIMARY KEY must have distinctive values.
- PRIMARY KEY cannot contain NULL values.
- A table can have only a single primary key.
- Syntax:
CREATE TABLE table_name (column1 datatype [ PRIMARY KEY ], column2 datatype [ NULL | NOT NULL ], ...);
or
CREATE TABLE table_name (column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ],...CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n));
Example:
CREATE TABLE student( id INT PRIMARY KEY, last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, CGPA float);
or
CREATE TABLE student( id INT , last_name VARCHAR(50) NOT NULL, first_name VARCHAR(50) NOT NULL, CGPA float, CONSTRAINT PRIMARY KEY(id));
Output:
SELECT * FROM [University].[dbo].[Student];
| id | last_name | first_name | CGPA |
PRIMARY KEY in ALTER TABLE
Syntax:
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1,column2,....column_n);
Example:
ALTER TABLE [University].[dbo].[student] ADD CONSTRAINT Student_pk PRIMARY KEY (id);
DROP PRIMARY KEY Constraint
Syntax:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Example:
ALTER TABLE [University].[dbo].[Student] DROP CONSTRAINT student_pk ;
MS SQL Server FOREIGN KEY
- FOREIGN KEY is a field in one table that indicates the primary key in another table.
- The table with the foreign key is called the child table, and also the table with the primary key is called the referenced or parent table.
- Syntax:
CREATE TABLE child_table ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ],... CONSTRAINT fk_name FOREIGN KEY (child_col1, child_col2, ... child_col_n) REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n) [ON DELETE {NO ACTION | CASCADE | SET NULL | SET DEFAULT} ][ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT} ]);
DROP a FOREIGN KEY
Syntax:
ALTER TABLE table_name DROP CONSTRAINT fk_name;
Example:
ALTER TABLE [University].[dbo].[student] DROP CONSTRAINT fk_inv_product_id;
