Tutorial SQL Server - 2. Table & Relationship

Table merupakan objek dalam database yang terdiri dari kolom & baris yang berfungsi untuk menyimpan data. Diagram database adalah representasi grafik dari sebuah database yg digunakan untuk mengelola objek-objek database dengan menggunakan antarmuka grafis. Sebelum membuat table, anda perlu mengetahui tipe data dalam sebuah kolom. Tipe data dalam SQL Server terlihat dalam tabel berikut : .:Ref Link:.
String types:

Data type	Description	Storage
char(n)	Fixed width character string. Maximum 8,000 characters	Defined width
varchar(n)	Variable width character string. Maximum 8,000 characters	2 bytes + number of chars
varchar(max)	Variable width character string. Maximum 1,073,741,824 characters	2 bytes + number of chars
text	Variable width character string. Maximum 2GB of text data	4 bytes + number of chars
nchar	Fixed width Unicode string. Maximum 4,000 characters	Defined width x 2
nvarchar	Variable width Unicode string. Maximum 4,000 characters
nvarchar(max)	Variable width Unicode string. Maximum 536,870,912 characters
ntext	Variable width Unicode string. Maximum 2GB of text data
bit	Allows 0, 1, or NULL
binary(n)	Fixed width binary string. Maximum 8,000 bytes
varbinary	Variable width binary string. Maximum 8,000 bytes
varbinary(max)	Variable width binary string. Maximum 2GB
image	Variable width binary string. Maximum 2GB

Number types:

Data type	Description	Storage
tinyint	Allows whole numbers from 0 to 255	1 byte
smallint	Allows whole numbers between -32,768 and 32,767	2 bytes
int	Allows whole numbers between -2,147,483,648 and 2,147,483,647	4 bytes
bigint	Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807	8 bytes
decimal(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
numeric(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
smallmoney	Monetary data from -214,748.3648 to 214,748.3647	4 bytes
money	Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
float(n)	Floating precision number data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.	4 or 8 bytes
real	Floating precision number data from -3.40E + 38 to 3.40E + 38	4 bytes

Date types:

Data type	Description	Storage
datetime	From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds	8 bytes
datetime2	From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds	6-8 bytes
smalldatetime	From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute	4 bytes
date	Store a date only. From January 1, 0001 to December 31, 9999	3 bytes
time	Store a time only to an accuracy of 100 nanoseconds	3-5 bytes
datetimeoffset	The same as datetime2 with the addition of a time zone offset	8-10 bytes
timestamp	Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Other data types:

Data type	Description
sql_variant	Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier	Stores a globally unique identifier (GUID)
xml	Stores XML formatted data. Maximum 2GB
cursor	Stores a reference to a cursor used for database operations
table	Stores a result-set for later processing

Sebelum kita lanjut belajar, silahkan buat dulu database yang akan kita gunakan sebagai media belajar. Contohnya seperti berikut :

USE master;
GO
CREATE DATABASE inventory
ON
(NAME = inventory_data, FILENAME = 'D:\learnSQL\inventory.mdf')
LOG ON
(NAME = inventory_log, FILENAME = 'D:\learnSQL\inventory.ldf');
GO

2.1 Create Table

Create table merupakan perintah untuk membuat table. Berikut cara membuat table dengan menggunakan SSMS :

1. Pada Object Explorer, Expand Item Databases kemudian expand <Nama Database> kemudian klik kanan Tables pilih New Table
2. Buat kolom dalam tabel tersebut dengan mengisi Column Name, memilih Data Type & menentukan Allow Nulls. Jika Allow Nulls di ceklist, maka data dalam kolom tersebut boleh dibiarkan kosong, dan sebaliknya.
3. Untuk membuat primary key, klik tombol Set Primary Key pada Table Designer Toolbar
4. Untuk membuat kolom Auto Increament, pilih kolom yg diinginkan kemudian pada window Column Properties cari item Identity Specification kemudian expand. Ubah nilai di parameter (Is Identity) dari No menjadi Yes
5. Untuk membuat constraints, pada kolom yg diinginkan klik tombol Manage Check Constraints pada Table Designer Toolbar. Pada form yg muncul klik tombol Add utk menambah contraint kemudian isi Expression kemudian klik Close
6. Untuk menyimpan table, klik tombol Save pada Standard Toolbar atau dengan shortcut Ctrl+S

Berikut cara untuk membuat table menggunakan T-SQL : .:Ref Link:.

USE inventory;
GO
CREATE TABLE T_Barang ( -- T_Barang adalah nama table
 id_Brng INT IDENTITY(0,1) NOT NULL PRIMARY KEY, -- kolom id_Brng merupakan primary key yg auto increament
 nm_brng VARCHAR(50) NOT NULL, -- jumlah maksimum karakter pada kolom nama barang = 50 dan nama barang harus diisi
 status_brng CHAR(1) NOT NULL DEFAULT '1', -- nilai default status_brng adalah '1'
 qty_brng NUMERIC(10,2) NOT NULL DEFAULT 0, -- nilai default qty_brng adalah 0.00
 desc_brng VARCHAR(250), -- jumlah maksimum karakter pada kolom desc_brng adalah 250 dan desc_brng boleh tidak diisi
 CONSTRAINT cekStatus CHECK (status_brng='0' OR status_brng='1') -- artinya status_brng hanya boleh diisi 0 atau 1
);
GO

NB : Untuk mengisi data dengan SSMS, pilih tabel yg ingin diisi, kemudian klik kanan pilih Edit Top 200 Rows. Untuk melihat data yang telah diisi dengan SSMS, pilih tabel yg ingin dilihat, kemudian klik kanan pilih Select Top 1000 Rows.

2.2 Alter Table (Go To Index)

Alter table merupakan perintah untuk mengubah table. Berikut cara mengubah table dengan menggunakan SSMS :

1. Pada Object Explorer, pilih tabel yang ingin kita ubah kemudian klik kanan dan pilih Desain
2. Pada tampilan yang muncul, silahkan ubah Column Name & Data Type & Allow Nulls
3. Setelah selesai, tekan tombol Save pada standart toolbar atau shortcut-nya Ctrl+S

Berikut cara untuk mengubah table menggunakan T-SQL : .:Ref Link:.

USE inventory;
GO
ALTER TABLE T_BARANG DROP COLUMN desc_brng;
GO -- artinya menghapus kolom desc_brng pada t_barang
ALTER TABLE T_BARANG ALTER COLUMN nm_brng VARCHAR(100) NOT NULL;
GO -- artinya mengubah tipe data kolom nm_brng dari varchar(50) menjadi varchar(100)
ALTER TABLE T_BARANG ADD tgl_input DATETIME NOT NULL DEFAULT GETDATE();
GO -- artinya menambah kolom tgl_input yg nilai defaultnya adalah tanggal sistem

2.3 Drop Table (Go To Index)

Drop table merupakan perintah untuk menghapus table. Berikut cara menghapus table dengan menggunakan SSMS :

1. Pada Object Explorer, pilih tabel yang ingin kita hapus kemudian klik Delete
2. Pada form Delete Object yang muncul, klik tombol OK

Berikut cara untuk menghapus table menggunakan T-SQL : .:Ref Link:.

USE inventory;
GO
DROP TABLE T_Barang;
GO

2.4 Relationship

Relationship merupakan hubungan antar table. Referensi dalam membuat relationship adalah sebagai berikut :

1. Create Foreign Key Relationships
2. Modify Foreign Key Relationships
3. Delete Foreign Key Relationships

Sebelum mempelajari tentang relationship, silahkan buat dulu tabel berikut untuk proses pembelajarannya karena tabel sebelumnya sudah kita hapus dengan perintah drop.

USE inventory;
GO
CREATE TABLE T_Barang (
 id_Brng INT IDENTITY(0,1) NOT NULL PRIMARY KEY,
 nm_brng VARCHAR(50) NOT NULL,
 status_brng CHAR(1) NOT NULL DEFAULT '1',
 qty_brng NUMERIC(10,2) NOT NULL DEFAULT 0,
 desc_brng VARCHAR(250),
 CONSTRAINT cekStatus CHECK (status_brng='0' OR status_brng='1')
);
GO
CREATE TABLE T_Jual (
 id_Jual INT IDENTITY(0,1) PRIMARY KEY,
 id_Brng INT NOT NULL
);
GO

Berikut cara membuat, mengubah & menghapus relationship foreign key dengan SSMS :

• Membuat Foreign Key
1. Pada desain view table yg akan dibuat foreign key, klik kanan kolom yg ingin jadi foreign key pilih Relationships.
2. Muncul form Foreign Key Relationships. Klik tombol Add
3. Pada bagian Tables And Columns Spesification, klik tombol Browse [...]
4. Muncul form Tables and Columns. Isi Relationship name, pilih Primary key table, pilih kolom primary key & foreign key kemudian klik OK
5. Pada form Foreign Key Relationships, klik tombol Close
• Mengubah Foreign Key
1. Pada Object Explorer, Expand Databases~<Nama Database>~<Nama Table>~<Keys>
2. Pilih keys yang ingin kita ubah kemudian klik kanan pilih Modify
3. Silahkan diubah data-data yang ingin diubah. Kemudian klik Close
• Menghapus Foreign Key
1. Pada Object Explorer, Expand Databases~<Nama Database>~<Nama Table>~<Keys>
2. Pilih keys yang ingin kita ubah kemudian klik kanan pilih Delete
3. Pada form Delete Object yg muncul, klik OK

Berikut cara membuat, mengubah & menghapus relationship foreign key dengan T-SQL :

• Membuat Foreign Key

USE inventory;
GO
ALTER TABLE T_Jual
ADD CONSTRAINT fk_id_brng_on_t_brng FOREIGN KEY (id_brng) -- fk_id_brng_on_t_brng adlh nama foreign key
REFERENCES T_barang(id_brng);
GO
        

• Mengubah Foreign Key
Untuk mengubah foreign key, caranya adalah dengan menghapus foreign key lama dan membuat ulang foreign key yg baru.
• Menghapus Foreign Key

   USE inventory;
   GO
   ALTER TABLE T_Jual
   DROP CONSTRAINT fk_id_brng_on_t_brng;
   GO