آموزش جامع SQL Server (جلسه ۱۲: تعریف داده‌ها – بخش ۱)
1398/03/07 15:24 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۱۲: تعریف داده‌ها – بخش ۱)

در این جلسه و جلسه آینده، چگونگی مدیریت مهم‌ترین اشیاء پایگاه داده از جمله دیتابیس‌ها و جداول را بررسی خواهیم کرد. مواردی که در این دو جلسه مورد بحث قرار می‌گیرند عبارت‌اند از:

  • CREATE DATABASE – چگونگی ایجاد یک دیتابیس جدید در SQL Server با استفاده از دستور CREATE DATABASE و SQL Server Management Studio را نمایش می‌دهد.

  • DROP DATABASE – چگونگی حذف دیتابیس‌های موجود را آموزش می‌دهد.

  • CREATE SCHEMA – چگونگی ایجاد یک شمای جدید در یک دیتابیس.

  • ALTER SCHEMA – چگونگی انتقال یک securable از یک شما به یک شمای موجود در همان دیتابیس را نمایش می‌دهد.

  • DROP SCHEMA: چگونگی حذف یک شما از یک دیتابیس را می‌آموزد.

  • CREATE TABLE – مراحل ایجاد یک جدول جدید در یک شمای خاص از یک دیتابیس را توضیح می‌دهد.

  • Identity column – چگونگی استفاده از ویژگی IDENTITY برای ایجاد ستون identity برای یک جدول.

  • Sequence یا توالی – چگونگی ایجاد یک توالی از مقادیر عددی بر اساس یک شرط یا شرایط خاص را توضیح می‌دهد.

  • ALTER TABLE ADD column – چگونگی اضافه کردن یک یا تعداد بیشتری ستون به یک جدول موجود را نمایش می‌دهد.

  • ALTER TABLE ALTER COLUMN – چگونگی تغییر تعریف ستون‌های موجود در یک جدول را نشان می‌دهد.

  • ALTER TABLE DROP COLUMN – چگونگی Drop کردن یک یا چند ستون از یک جدول را آموزش می‌دهد.

  • Computed columns – چگونگی استفاده از ستون‌های محاسبه‌ای برای استفاده مجدد از منطق محاسبه‌ای در چندین query را توضیح می‌دهد.

  • DROP TABLE – چگونگی حذف جداول از دیتابیس را نمایش می‌دهد.

  • TRUNCATE TABLE – همه داده‌های یک جدول را خیلی سریع‌تر و مؤثرتر حذف می‌کند.

  • SELECT INTO – چگونگی ایجاد یک جدول و وارد کردن داده از یک query به داخل آن را آموزش می‌دهد.

  • Rename a table – فرآیند تغییر نام یک جدول را توضیح می‌دهد.

  • Temporary tables – شما را با جداول موقتی مفید برای مرتب‌سازی داده‌های آنی موقتی در stored procedureها یا database session آشنا می‌کند.

  • Synonym - هم‌معنی یا synonym را توضیح داده و چگونگی ایجاد هم‌معنی‌ها برای اشیاء پایگاه داده را نمایش می‌دهد.

این موارد در طی دو جلسه به طور کامل و مثال محور بررسی خواهند شد. با ما همراه باشید!

دستور CREATE DATABASE در SQL Server

دستور CREATE DATABASE یک پایگاه داده یا دیتابیس جدید ایجاد می‌کند. کد زیر حداقل syntax دستور CREATE DATABASE را نشان می‌دهد:

CREATE DATABASE database_name;

در این syntax، نام پایگاه داده پس از کلیدواژه CREATE DATABASE مشخص می‌شود.

نام پایگاه داده باید در یک نمونه SQL Server منحصر به فرد باشد. همچنین این نام باید با قوانین خاص SQL Server نیز همخوانی داشته باشد. معمولاً، نام پایگاه داده می‌تواند حداکثر ۱۲۸ حرف باشد.

کد زیر یک پایگاه داده جدید به نام TestDb ایجاد می‌کند.

CREATE DATABASE TestDb;

وقتی دستور با موفقیت اجرا شود، می‌توانید پایگاه داده به تازگی ایجاد شده را در Object Explorer ببینید. اگر پایگاه داده جدید ظاهر نشد، می‌توانید روی کلید Refresh کلیک کنید یا کلید F5 کیبورد را بزنید تا لیست اشیاء به روز رسانی شود.

اگر پایگاه داده جدید ظاهر نشد، می‌توانید روی کلید Refresh کلیک کنید

کد زیر تمام پایگاه داده‌های موجود در SQL Server را لیست می‌کند:

SELECT 
    name
FROM 
    master.sys.databases
ORDER BY 
    name;

نتایج

یا می‌توانید یک stored procedure به نام sp_databases را ایجاد کنید:

EXEC sp_databases;

ایجاد یک پایگاه داده جدید با استفاده از SQL Server Management Studio

ابتدا، روی Database کلیک راست کنید و بعد گزینه … New Database را انتخاب کنید.

روی Database کلیک راست کنید و بعد گزینه … New Database را انتخاب کنید.

دوم، نام پایگاه داده (برای مثال SampleDb) را وارد کنید و روی کلید OK کلیک کنید.

نام پایگاه داده (برای مثال SampleDb) را وارد کنید و روی کلید OK کلیک کنید.

سوم، می‌توانید پایگاه داده به تازگی ایجاد شده را در Object Explorer مشاهده کنید.

می‌توانید پایگاه داده به تازگی ایجاد شده را در Object Explorer مشاهده کنید

به این شکل، چگونگی ایجاد یک پایگاه داده جدید با استفاده از دستور CREATE DATABASE و SQL Server Management Studio را آموختید.

دستور DROP DATABASE در SQL Server

حالا قصد داریم چگونگی حذف کردن یک پایگاه داده در یک نمونه SQL Server را با استفاده از دستور DROP DATABASE و SQL Server Management Studio را توضیح دهیم.

توجه کنید که در این قسمت از پایگاه داده‌های TestDb و SampleDb ایجاد شده در قسمت قبلی برای مثال‌ها استفاده می‌کنیم.

استفاده از دستور DROP DATABASE برای حذف یک پایگاه داده

برای حذف یک پایگاه داده موجود از یک نمونه SQL Server، از دستور DROP DATABASE استفاده می‌کنیم.

دستور DROP DATABASE به شما اجازه می‌دهد یک یا تعداد بیشتری از پایگاه‌های داده را با استفاده از Syntax زیر حذف کنید:

DROP DATABASE  [ IF EXISTS ]
    database_name 
    [,database_name2,...];

در این syntax، نام پایگاه داده‌ای که قرار است Drop شود، بعد از کلیدواژه DROP DATABASE مشخص می‌شود.

اگر می‌خواهید چندین پایگاه داده را با استفاده از یک دستور حذف یا drop کنید، می‌توانید بعد از کلید واژه DROp DATABASE از یک لیست حاوی نام‌های پایگاه داده که در آن اسامی با ویرگول از هم جدا شده‌اند، استفاده کنید.

قسمت IF EXISTS از SQL Server 2016 به بعد در دسترس کاربران قرار گرفت. این قسمت به شما اجازه می‌دهد تا برای حذف یک پایگاه داده شرط بگذارید (فقط در صورتی که آن پایگاه داده وجود داشته باشد).

اگر سعی کنید یک پایگاه داده ناموجود را بدون استفاده از قسمت IF EXISTS حذف کنید، SQL Server ارور خواهد داد.

قبل از حذف یک پایگاه داده، باید ابتدا شروط زیر را بررسی کنید:

  • اول، دستور DROP DATABASE پایگاه داده و همچنین فایل‌های دیسک فیزیکی مورد استفاده توسط پایگاه داده را حذف کند. در نتیجه، باید یک فایل پشتیبانی یا بکاپ از پایگاه داده داشته باشید تا اگر در آینده به آن نیاز پیدا کردید دچار مشکل نشوید.
  • دوم، نمی‌توانید پایگاه داده‌ای که در حال استفاده است را حذف کنید.

سعی برای drop کردن یک پایگاه داده در حال استفاده باعث بروز این ارور می‌شود:

Cannot drop database "database_name" because it is currently in use.

مثال زیر از دستور DROP DATABASE برای حذف پایگاه داده TestDb استفاده می‌کند:

DROP DATABASE IF EXISTS TestDb;

استفاده از SQL Server Management Studio برای drop کردن یک پایگاه داده

برای حذف پایگاه داده SampleDb باید مراحل زیر را طی کنید.

اول، روی نام پایگاه داده‌ای که می‌خواهید حذف شود کلیک راست کنید و گزینه Delete را انتخاب کنید:

روی نام پایگاه داده‌ای که می‌خواهید حذف شود کلیک راست کنید و گزینه Delete را انتخاب کنید

دوم، تیک گزینه Delete backup and restore history information for databases را بردارید و در عوض تیک گزینه Close existing connections را بزنید و روی Ok کلیک کنید تا پایگاه داده حذف شود.

تیک گزینه Delete backup and restore history information for databases را بردارید و در عوض تیک گزینه Close existing connections را بزنید

سوم، مطمئن شوید که پایگاه داده از لیست Object Explorer حذف شده باشد.

مطمئن شوید که پایگاه داده از لیست Object Explorer حذف شده باشد

به این شکل، چگونگی استفاده از دستور DROP DATABASE و SQL Server Management Studio برای حذف پایگاه داده‌ها از SQL Server را آموختید.

دستور CREATE SCHEMA در SQL Server

در این قسمت نحوه استفاده از دستور CREATE SCHEMA برای ایجاد یک شمای جدید در پایگاه داده فعلی را توضیح خواهیم داد.

شما در SQL Server چیست؟

شما مجموعه‌ای از اشیاء پایگاه داده از جمله جداول، viewها، triggerها، stored procedureها، indexها و غیره است. شما دارای یک username است که به عنوان مالک شما شناخته می‌شد، این شخص مالک تمام اشیاء پایگاه داده که از لحاظ منطقی به آن مربوط هستند نیز می‌باشد.

شما همیشه به یک پایگاه داده تعلق دارد. از طرف دیگر، یک پایگاه داده ممکن است یک یا چند شما داشته باشد. برای مثال، در پایگاه داده نمونه Bikestores، ما دو شما به نام‌های sales و production داریم.

یک شیء درون یک شما با استفاده از فرمت schema_name.object_name (مانند sales.orders) شناسایی و فراخوانی می‌شود. دو جدول در دو شما می‌توانند دارای نام‌های مشابه باشند. بنابراین مثلاً می‌توانید hr.employees و sales.employees داشته باشید.

شماهای داخلی (Built-in) در SQL Server

SQL Server چند شمای از پیش تعریف شده در اختیار ما قرار می‌دهد که دارای نام‌های مشابه با کارایی‌ها و قوانین داخلی پایگاه داده هستند، برای مثال: sys، guest، dbo و INFORMATION_SCHEMA.

توجه کنید که SQL Server شماهای sys و INFORMATION_SCHEMA را برای اشیاء سیستم استفاده می‌کند، در نتیجه نمی‌توانید هیچ شیئی در این شماها ایجاد یا حذف کنید.

شمای پیش‌فرض برای یک پایگاه داده جدیداً ایجاد شده dbo نام دارد، که متعلق به حساب کاربر dbo است. به طور پیش‌فرض، وقتی یک کاربر جدید با استفاده از دستور CREATE USER ایجاد می‌کنید، کاربر شمای Dbo را به عنوان شمای پیش‌فرض دریافت می‌کند.

بررسی دستور CREATE SCHEMA در SQL Server

دستور CREATE SCHEMA به شما اجازه می‌دهد یک شمای جدید در پایگاه داده فعلی ایجاد کنید.

کد زیر ورژن ساده‌سازی شده از دستور CREATE SCHEMA را نشان می‌دهد:

CREATE SCHEMA schema_name
    [AUTHORIZATION owner_name]

در این syntax:

  • ابتدا، نام شمایی که می‌خواهید در دستور CREATE SCHEMA ایجاد کنید را مشخص می‌کنید.
  • سپس، مالک شما را پس از کلیدواژه AUTHORIZATION مشخص می‌کنید.

مثال دستور CREATE SCHEMA در SQL Server

مثال زیر چگونگی استفاده از دستور CREATE SCHEMA برای ایجاد شمای customer_services را نشان می‌دهد:

2
CREATE SCHEMA customer_services;
GO

توجه کنید که دستور GO به SQL Server Management Studio دستور می‌دهد تا کدهای SQL را به دستور موجود در سرور ارسال کند تا اجرا شوند.

وقتی دستور اجرا شد، می‌توانید شمای جدیداً ایجاد شده را در زیر قسمت Security>Schemas در نام پایگاه داده بیابید.

می‌توانید شمای جدیداً ایجاد شده را در زیر قسمت Security>Schemas در نام پایگاه داده بیابید

اگر می‌خواهید تمام شماهای موجود در پایگاه داده فعلی را لیست کنید، می‌توانید آن‌ها با استفاده از query زیر از sys.schema به دست‌ آورید:

SELECT 
    s.name AS schema_name, 
    u.name AS schema_owner
FROM 
    sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY 
    s.name;

نتایج

بعد از به دست آوردن customer_services، می‌توانید چند شیء برای شما ایجاد کنید. برای مثال، کد زیر یک جدول جدید به نام jobs در شمای customer_services ایجاد می‌کند:

CREATE TABLE jobs(
    job_id INT PRIMARY KEY IDENTITY,
    customer_id INT NOT NULL,
    description VARCHAR(200),
    created_at DATETIME2 NOT NULL
);

به این صورت، چگونگی استفاده از دستور CREATE SCHEMA برای ایجاد یک شمای جدید در پایگاه داده فعلی را آموختید.

دستور ALTER SCHEMA در SQL Server

در این قسمت قصد داریم چگونگی استفاده از دستور ALTER SCHEMA برای انتقال یک securable از یک شما به یک شمای دیگر را توضیح دهیم.

بررسی دستور ALTER SCHEMA در SQL Server

دستور ALTER SCHEMA به شما اجازه می‌دهد یک securable را از یک شما به یک شمای دیگر موجود در یک پایگاه داده مشترک انتقال دهید.

توجه کنید که Securable یک منبع است که به واسطه آن موتور پایگاه داده (Database Engine) دسترسی کنترل‌های سیستم را مشخص می‌کند. برای مثال، جدول یک securable است.

کد زیر syntax دستور ALTER SCHEMA را نشان می‌دهد:

ALTER SCHEMA target_schema_name   
    TRANSFER [ entity_type :: ] securable_name;

در این suyntax:

  • Target_schea_name نام یک شما در پایگاه داده فعلی است، که در آن قصد دارید یک شیء را جابه‌جا کنید. توجه کنید که این شما نمی‌تواند SYS یا INFORMATION_SCHEMA باشد.

  • Entity_type می‌تواند یک شیء، نوع یا یک مجموعه شمای XML باشد. به صورت پیش‌فرض یک شیء است. Entity_type کلاس entity یا موجودیتی را نشان می‌دهد که مالک آن قرار است تغییر کند.

  • Object_name نام securable است که می‌خواهید آن را به target_schema_name منتقل کنید.

اگر یک view، function، stored procedure یا trigger را جابه‌جا کنید، SQL Server نام شمای این securableها را تغییر نمی‌دهد.

در نتیجه، توصیه می‌شود که این اشیاء را به جای اینکه با استفاده از دستور ALTER SCHEMA جابه‌جا کنید، آن‌ها را در شمای جدید drop و مجدداً ایجاد کنید.

اگر یک شیء (برای مثال جدول یا synonym) را جابه‌جا کنید، SQL Server رفرنس‌های این اشیاء را به صورت خودکار به روز رسانی نمی‌کند. باید به صورت دستی رفرنس‌ها را اصلاح کنید تا با نام شمای جدید تطابق یابند.

برای مثال، اگر یک جدول را که به سمت یک stored procedure رفرنس کرده است را جابه‌جا کنید، باید stored procedure را تغییر دهید تا با نام شمای جدید مطابقت داشته باشد.

مثال ALTER SCHEMA در SQL Server

ابتدا، یک جدول جدید به نام offices در شمای dbo ایجاد کنید:

CREATE TABLE dbo.offices
(
    office_id      INT
    PRIMARY KEY IDENTITY, 
    office_name    NVARCHAR(40) NOT NULL, 
    office_address NVARCHAR(255) NOT NULL, 
    phone          VARCHAR(20),
);

بعد، چند ردیف به جدول dbo.offices وارد کنید:

INSERT INTO 
    dbo.offices(office_name, office_address)
VALUES
    ('Silicon Valley','400 North 1st Street, San Jose, CA 95130'),
    ('Sacramento','1070 River Dr., Sacramento, CA 95820');

سپس، یک stored procedure ایجاد کنید که office را با استفاده از office id بیابد:

CREATE PROC usp_get_office_by_id(
    @id INT
) AS
BEGIN
    SELECT 
        * 
    FROM 
        dbo.offices
    WHERE 
        office_id = @id;
END;

پس از آن، جدول dbo.offices را به شمای sales منتقل کنید:

ALTER SCHEMA sales TRANSFER OBJECT::dbo.offices;  

اگر stored procedure با نام usp_get_office_by_id را اجرا کنید، SQL Server ارور زیر را می‌دهد:

Msg 208, Level 16, State 1, Procedure usp_get_office_by_id, Line 5 [Batch Start Line 30]
Invalid object name 'dbo.offices'.

در آخر، به صورت دستی stored procedure را تغییر دهید تا با نام شمای جدید مطابقت داشته باشد:

ALTER PROC usp_get_office_by_id(
    @id INT
) AS
BEGIN
    SELECT 
        * 
    FROM 
        sales.offices
    WHERE 
        office_id = @id;
END;

در نتیجه به این شکل، چگونگی استفاده از دستور ALTER SCHEMA در SQL Server برای انتقال یک Securable از یک شما به یک شمای دیگر در یک پایگاه داده مشابه را آموختید.

دستور DROP SCHEMA در SQL Server

در این قسمت چگونگی استفاده از دستور DROP SCHEMA برای حذف یک شما از پایگاه داده را توضیح خواهیم داد.

بررسی دستور DROP SCHEMA در SQL Server

دستور DROP SCHEMA به شما اجازه می‌دهد تا یک شما را از یک پایگاه داده حذف کنید. کد زیر syntax دستور DROP SHCEMA را نمایش می‌دهد:

DROP SCHEMA [IF EXISTS] schema_name;

در این syntax:

  • ابتدا، نام شمایی که می‌خواهید حذف شود مشخص شده است. اگر شما حاوی شیء باشد، دستور اجرا نمی‌شود. در نتیجه، باید تمام اشیاء شما را قبل از حذف آن پاک کنید.

  • سپس از دستور IF EXISTS برای حذف شرطی شما (در صورت وجود داشتن آن شما) استفاده شده است. تلاش برای حذف یک شمای نا موجود بدون استفاده از دستور IF EXISTS باعث بروز ارور می‌شود.

مثال دستور DROP SCHEMA در SQL Server

ابتدا، یک شمای جدید به نام logistics ایجاد کنید:

CREATE SCHEMA logistics;
GO

بعد، یک جدول جدید به نام Deliveries درون شمای logistics ایجاد کنید:

CREATE TABLE logistics.deliveries
(
    order_id        INT
    PRIMARY KEY, 
    delivery_date   DATE NOT NULL, 
    delivery_status TINYINT NOT NULL
);

سپس، شمای logistics را حذف کنید:

DROP SCHEMA logistics;

مسلماً SQL Server ارور زیر را می‌دهد، چون شما خالی نیست:

Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'logistics' because it is being referenced by object 'deliveries'.

حالا، جدول logistics.deliveries را حذف کنید:

DROP TABLE logistics.deliveries;

در آخر، دوباره از دستور DROP SCHEMA برای حذف شمای logistics استفاده کنید:

DROP SCHEMA IF EXISTS logistics;

حالا، متوجه خواهید شد که شمای logistics از پایگاه داده حذف شده است.

به این صورت، چگونگی استفاده از دستور DROP SCHEMA برای حذف یک شما از یک پایگاه داده را آموختید.

دستور CREATE TABLE در SQL Server

در این قسمت چگونگی استفاده از دستور CREATE TABLE برای ایجاد یک جدول را توضیح می‌دهیم.

معرفی دستور CREATE TABLE

جداول برای ذخیره داده‌ها در پایگاه داده استفاده می‌شوند. جداول دارای نام منحصر به فرد درون یک پایگاه داده و شما هستند.

هر جدول حاوی یک چند ستون است و هر ستون دارای یک نوع داده مرتبط است که نوع داده‌ای که می‌تواند ذخیره کند (مانند اعداد، رشته‌ها یا داده موقت) را تعریف می‌کند.

برای ایجاد یک جدول جدید، می‌توانید از دستور CREATE TABLE به شکل زیر استفاده کنید:

CREATE TABLE [database_name.][schema_name.]table_name (
    pk_column data_type PRIMARY KEY,
    column_1 data_type NOT NULL,
    column_2 data_type,
    ...,
    table_constraints
);

در این syntax:

  • اول، نام پایگاه داده‌ای که می‌خواهید در آن جدول ایجاد کنید مشخص شده است database_name باید نام یک پایگاه داده موجود باشد. اگر این نام را مشخص نکنید، database_name به صورت پیش‌فرض پایگاه داده فعلی را در نظر می‌گیرد.

  • دوم، شمایی که جدول جدید به آن تعلق دارد مشخص شده است.

  • سوم، نام جدول جدید مشخص شده است.

  • چهارم، هر جدول باید یک primary key داشته باشد که شامل یک یا چند ستون است. معمولاً، ابتدا ستون‌های primary key مشخص می‌شوند، بعد بقیه ستون‌ها قرار می‌گیرند. اگر primary key فقط شامل یک ستون باشد، می‌توانید از کلیدواژه‌ primary key پس از نام ستون استفاده کنید.

    اگر primary key شامل دو یا تعداد بیشتری ستون باشد، باید ویژگی primary key را به عنان یک ویژگی جدول مشخص کنید. هر ستون دارای یک نوع داده مرتبط است که پس از نام آن جدول در کد می‌آید.

    یک ستون می‌تواند یک یا تعداد بیشتری ویژگی ستون (مانند NOT NULL و UNIQUE) داشته باشد.

  • پنجم، یک جدول می‌تواند چند ویژگی مشخص شده در بخش ویژگی‌های جدول (table constraints) داشته باشد، مانند NIQUUE، PRIMARY KEY، FOREIGN KEY و CHECK.

توجه کنید که دستور CREATE TABLE پیچیده است و دارای آپشن‌ها بیشتری از آنچه در syntax بالا نمایش داده شده است، می‌باشد. در تمرین‌ و مثال‌‌های دیگر در طول دوره، بقیه این آپشن‌ها را توضیح خواهیم داد.

مثال CREATE TABLE در SQL Server

کد زیر یک جدول جدید به نام Sales.visits برای پیگیری بازدیدهای مشتری از فروشگاه ایجاد می‌کند:

CREATE TABLE sales.visits (
    visit_id INT PRIMARY KEY IDENTITY (1, 1),
    first_name VARCHAR (50) NOT NULL,
    last_name VARCHAR (50) NOT NULL,
    visited_at DATETIME,
    phone VARCHAR(20),
    store_id INT NOT NULL,
    FOREIGN KEY (store_id) REFERENCES sales.stores (store_id)
);

در این مثال:

چون نام پایگاه داده‌ای که قرار است جدول در آن ایجاد شود را به صورت واضح مشخص نمی‌کنیم، پس جدول visits در پایگاه داده Bikestores ایجاد می‌شود. برای شما، آن را به وضوح مشخص کرده‌ایم، در نتیجه جدول visits در شمای sales ایجاد شده است.

جدول visits حاوی ۶ ستون است:

  • ستون visit_id ستون primary key در جدول است. (1,1)IDENTITY به SQL Server می‌گوید که به صورت خودکار اعداد صحیح برای ستون ایجاد کرده، از عدد ۱ شروع کند و یکی‌یکی به عدد هر ردیف اضافه شود.

  • ستون‌های first_name و last_name ستون‌های رشته‌ای و حرفی با نوع VARCHAR هستند. این ستون‌ها می‌توانند تا ۵۰ حرف را در خود ذخیره کنند.

  • Visited)id یک ستون DATETIME است که تاریخ و زمانی که مشتری از فروشگاه دیدار کرده است را ثبت می‌کند.

  • ستون phone یک ستون رشته‌ای حرفی متفاوت است که مقدار NULL می‌پذیرد.

  • ستون store_id اعداد id را ذخیره می‌کند که این اعداد مشخص می‌کنند مشتری به کدام فروشگاه سر زده است.

  • در انتهای تعریف جدول یک ویژگی FOREIGN KEY قرار دارد. این کلید خارجی اطمینان می‌دهد که مقادیر درون ستون store_id از جدول visits حتماً در ستون store_id در جدول stores نیز وجود داشته باشند. در قسمت بعدی بیشتر با کلید خارجی آشنا خواهید شد.

در نتیجه به این شکل، چگونگی استفاده از دستور CREATE TABLE برای ایجاد یک جدول جدید در یک پایگاه داده را آموختید.

ویژگی Identity در SQL Server

در این قسمت چگونگی استفاده از ویژگی IDENTITY در SQL Server برای افزودن یک ستون identity به یک جدول را توضیح خواهیم داد.

معرفی ستون IDENTITY در SQL Server

برای ایجاد یک ستون identity برای یک جدول، می‌توانید از ویژگی IDENTITY به شکل زیر استفاده کنید:

IDENTITY[(seed,increment)]

در این Syntax:

  • Seed مقدار اولین ردیف بارگذاری شده درون جدول است.
  • Increment مقداری است که به مقدار identity در ردیف قبلی اضافه می‌شود.

مقدار پیش‌فرض seed و increment ۱ است (مانند (1,1)). این یعنی اولین ردیفی که درون جدول بارگذاری شود، مقدار یک را خواهد داشت، دومین ردیف مقدار ۲ و الی آخر.

فرض کنید، می‌خواهید مقدار ستون identity برای اولین ردیف ۱۰ باشد و مقدار افزایشی نیز ۱۰ است، پس باید از syntax زیر استفاده کنید:

IDENTITY (10,10)

توجه کنید که SQL Server به شما اجازه می‌دهد تا فقط یک ستون identity برای هر جدول داشته باشید.

مثال IDENTITY در SQL Server

اجازه دهید یک شمای جدید به نام hr برای تمرین ایجاد کنیم:

CREATE SCHEMA hr;

کد زیر با استفاده از ویژگی IDENTITY برای ستون id شخصی یک جدول جدید ایجاد می‌کند:

CREATE TABLE hr.person (
    person_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) NOT NULL
);

ابتدا، یک ردیف جدید برای جدول person وارد کنید:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('John','Doe', 'M');

خروجی به این شکل است:

نتایج

همان‌طور که به وضوح از خروجی مشخص است، اولین ردیف با مقدار ۱ در ستون preson_id بارگذاری شده است.

سپس، یک ردیف دیگر به جدول person اضافه شده است:

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Jane','Doe','F');

خروجی به این شکل است:

نتایج

همان‌طور که به وضوح در خروجی مشخص است، دویم ردیف دارای مقدار ۲ در ستون person_id است.

استفاده مجدد از مقادیر identity

SQL Server از مقادیر identity مجدداً استفاده نمی‌کند. اگر یک ردیف به ستون identity اضافه کنید و دستور insert به هر دلیلی اجرا نشود، آنگاه مقدار identity از دست می‌رود و دیگر ایجاد نمی‌شود. این امر باعث به وجود آمدن فضاهای خالی در ستون identity می‌شود.

مثال زیر را در نظر بگیرید.

ابتدا، دو جدول دیگر در شمای hr به نام‌های position و person_position ایجاد کنید:

CREATE TABLE hr. POSITION (
 position_id INT IDENTITY (1, 1) PRIMARY KEY,
 position_name VARCHAR (255) NOT NULL,
 
);
 
CREATE TABLE hr.person_position (
 person_id INT,
 position_id INT,
 PRIMARY KEY (person_id, position_id),
 FOREIGN KEY (person_id) REFERENCES hr.person (person_id),
 FOREIGN KEY (position_id) REFERENCES hr. POSITION (position_id)
);

سپس، یک شخص جدید اضافه کنید و با وارد کردن یک ردیف جدید درون جدول preson_position به آن شخص یک موقعیت شغلی اختصاص دهید:

BEGIN TRANSACTION
    BEGIN TRY
        -- insert a new person
        INSERT INTO hr.person(first_name,last_name, gender)
        VALUES('Joan','Smith','F');
 
        -- assign the person a position
        INSERT INTO hr.person_position(person_id, position_id)
        VALUES(@@IDENTITY, 1);
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0  
            ROLLBACK TRANSACTION;  
    END CATCH
 
    IF @@TRANCOUNT > 0  
        COMMIT TRANSACTION;
GO

در این مثال، اولین کد insert با موفقیت اجرا شد. اما، دومین کد به خاطر اینکه هیچ موقعیت شغلی در id شماره یک در جدول position وجود نداشت درست اجرا نشد. به خاطر ارور پیش آمده، کل فرآیند متوقف شد.

چون اولین دستور INSERT مقدار identity سه را داشت و کل فرآیند متوقف شد، پس مقدار identity بعدی همان‌طور که در کد زیر مشاهده می‌کنید چهار است:

INSERT INTO hr.person(first_name,last_name,gender)
OUTPUT inserted.person_id
VALUES('Peter','Drucker','F');

خروجی کد به این شکل است:

نتایج

به این شکل، چگونگی استفاده از ویژگی IDENTITY در SQL Server برای ایجاد یک ستون identity برای یک جدول را آموختید.

 

خلاصه

در این جلسه وارد مبحث تعریف داده‌ها شدیم که شامل دستورات، ویژگی‌ها و روش‌های زیادی بود و تصمیم بر این شد که در دو جلسه این موارد پوشش داده شوند.

در این جلسه به توضیح sequenceها،Identity، CREATE TABLE، DROP SCHEMA، ALTER SCHEMA، CREATE SCHEMA، DROP DATABASE، CREATE DATABASE و ALTER TABLE ADD پرداختیم و در جلسه آینده نیز به توضیح ادامه این موارد خواهیم پرداخت.

با MUG همراه باشید.

منبع: sqlservertutorial

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات