SQL: Creating, Using, and Removing Databases

Creating a Database

• Syntax to create a database uses: CREATE DATABASE database_name
• Example: CREATE DATABASE samplelecture
• Following successful database creation, select the database to use before creating tables
• The default selected database is master
• There are two methods for selecting the database:
  • Using the USE DATABASE statement. Syntax: USE database_name. Example: USE samplelecture
  • By clicking on the database list in the interface

Removing a Database

• Before deleting a database, ensure that the database to be deleted is not currently selected
• Syntax for removing a database: DROP DATABASE database_name
• Example: DROP DATABASE samplelecture

Creating a Database Table

• Select the correct database before executing the structure of your database table
• Syntax:
  • CREATE TABLE table_name( column_name1 datatype, column_name2 datatype, column_name3 datatype, ... ) [table constraints, if any]

Common Constraints

• PRIMARY KEY ensures the uniqueness of every record in a table and is required in every table
• FOREIGN KEY ... REFERENCES enforces referential integrity, ensuring a value inserted in the foreign key column matches a value stored in the primary key column of the reference table
• NULL | NOT NULL determines if null values are allowed in a column. If a value is required, NOT NULL should be placed after the data type declaration
• IDENTITY is used to provide a unique, incremental value for a column, commonly used with primary key constraints. Example: StudID int primary key identity (1000,1)
• DEFAULT provides a default value for a column when a value isn't given by the user during insertion. Example: HireDate datetime not null default (getdate())
• CHECK can limit or filter the possible values entered into a column. Example: InstructorType varchar(1) check(InstructorType in (ʻR','P'))
• You cannot add two primary keys in a table at the same time in one declaration.
• To set two primary keys in a table, you have to define first the columns and its datatype with NOT NULL values and then add a separate constraint for the two columns that you wanted to be set as the primary keys

Viewing Database Diagram on SQL Server

• On the Object Explorer, expand the Databases folder
• Expand the folder of the database where needed table exists
• Right-click on Database Diagrams and choose New Database Diagram
• In the Add Table dialog box, select the tables to include in the diagram and click Add

Deleting a Table

• Syntax: DROP TABLE table_name
• Example: DROP TABLE COLLEGE
• To check if the table has been deleted, regenerate a new database diagram
• Refresh the database first, as there might be a delay in the generation of the new diagram

Linking Tables with Constraints

• Link tables by using constraints. For example, the FACULTY table contains a ColCode attribute, which becomes a foreign key referencing the ColCode in the COLLEGE table
• The SupervisorID in the FACULTY table references the EmpID as its primary key within the same table
• Since tables can no longer have constraints added after the column declaration and data type, alter the structure of the table in order to do it

Adding a Foreign Key Constraint

• Syntax:
  • ALTER TABLE table_name
  • ADD CONSTRAINT constraint_name
  • FOREIGN KEY (column_name)
  • REFERENCES reference_table_name(reference_column_name)
• Constraint for the colcode in the faculty table referencing the colcode in the college table:
  • alter table FACULTY
  • add constraint colcode_fk foreign key(colcode) references college(colcode)
• Constraint for the supervisorID in the table faculty referencing the EmpID in the same table:
  • alter table FACULTY
  • add constraint sup_fk foreign key(supervisorID) references Faculty(empID)
• Drag the link to its respective primary key and foreign key when setting up the new database diagram
• The key represents the primary key and the infinite sign represents the foreign key. The colcode_fk constraint represents the relationship between the two tables, while the sup_fk constraint represents the relationship in the same table
• When adding a foreign key constraint, ensure that the data type matches its referencing primary key

Deleting a Constraint from a Table

• Syntax: ALTER TABLE table_name
• DROP CONSTRAINT constraint_name
• Example: ALTER TABLE FACULTY
• DROP CONSTRAINT sup_fk
• Refresh and generate a new database table to confirm deletion. Only the colcode_fk constraint should remain

Adding a Column to an Existing Table

• Syntax: ALTER TABLE table_name
• ADD column_name datatype
• Example: ALTER TABLE COLLEGE
• ADD ColDesc varchar(50)

Deleting a Column to an Existing Table

• Syntax: ALTER TABLE table_name
• DROP COLUMN column_name
• Example: ALTER TABLE COLLEGE
• DROP COLUMN ColDesc

Changing the Column Definition of an Existing Table

• For example, altering the maximum number of characters accepted in the ColName column of the COLLEGE table from varchar(20) to varchar(30)
• Syntax: ALTER TABLE table_name
• ALTER COLUMN column_name new_datatype
• Example: ALTER TABLE COLLEGE
• ALTER COLUMN ColName varchar(30)

Adding a Primary Key Constraint to an Existing Table

• Assume the COLLEGE table has been created, but the primary key was not indicated
• Syntax:
  • ALTER TABLE table_name
  • ADD CONSTRAINT constraint_name
  • PRIMARY KEY (column_name)
• Example:
  • ALTER TABLE COLLEGE
  • ADD CONSTRAINT col_pk
  • PRIMARY KEY (ColCode)
• Ensure NOT NULL is set to the declaration of the column before adding the constraint