گذاشتن Primary Key روي Computed Field

[A.Farzin]

با سلام
اگر يك فيلد محاسباتي داشته باشيم از آنجا كه اين فيلد را نميتوان NOT NULL كرد پس اجازه گذاشتن Primary Key را هم روي آن نميدهد.
من نياز دارم يك فيلد محاسباتي را به عنوان كليد اصلي جدول تعريف كنم. آيا ممكن است راهنمايي كنيد. ممنون.

CREATE TABLE mytable
(FK1 int NOT NULL,
FK2 int NOT NULL,
ID AS (FK1 * 100000) + FK2);
در مثال بالا ميخواهم ستون ID كليد اصلي جدول باشد.

[محمد سلیم آبادی]

چرا می خواهید از این ستون به عنوان primary key استفاده کنید؟

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


CREATE TABLE [dbo].[mytable](
[FK1] [int] NOT NULL,
[FK2] [int] NOT NULL,
[ID] AS ([FK1]*(100000)+[FK2])
PERSISTED
PRIMARY KEY
) ON [PRIMARY]

GO

[A.Farzin]

چرا می خواهید از این ستون به عنوان primary key استفاده کنید؟
علت اين كار اين است كه مي‌خواهم از روي كليد خارجي هم (بدون مراجعه به جدول PK) برخي از اطلاعات را استخراج كنم. به عنوان مثال اگر شماره روز + ساعت + ثانيه را با شكل (2640840) در هم تلفيق كنم يك كد 7 رقمي خواهم داشت كه از سمت راست به چپ، سه عدد اول شماره روز، دو عدد بعدي ساعت و دو عدد بعدي ثانيه را نشان خواهد داد. با اين حال براي فهميدن اين چند اطلاع نيازي به مراجعه به جدول اصلي نخواهم داشت.
در كليه حالتهايي كه بخواهيم به جاي IDENTITY از يك كد بلوكه بندي شده، مانند مثال بالا، شماره دانشجوئي، كد ملي، و ... استفاده كنيم چنين چيزي مورد نياز خواهد بود.
ضمناً از پاسختان ممنونم.

[محمد سلیم آبادی]

در كليه حالتهايي كه بخواهيم به جاي IDENTITY از يك كد بلوكه بندي شده، مانند مثال بالا، شماره دانشجوئي، كد ملي، و ... استفاده كنيم چنين چيزي مورد نياز خواهد بود.
ضمناً از پاسختان ممنونم.

ایده ی جالبی است، شماره دانشجویی از این نمونه ها است، مثلا سه رقم اول کد دانشگاه ، سه رقم بعدی شماره ردیف، سه رقم بعدی سال ورود و....

[A.Farzin]

با سلام
مثل اينكه PERSISTED در 2005 به بعد اضافه شده و در 2000 كار نميكند؟
راه‌حل در 2000 ولي با يك مشكل جديد:
در 2000 مي‌توان به شكل زير يك Primary Key روي Computed Field گذاشت.

CREATE TABLE [dbo].[mytable](
[FK1] [int] NOT NULL,
[FK2] [int] NOT NULL,
[ID] AS ISNULL(([FK1]*100000)+[FK2], 0)
PRIMARY KEY
) ON [PRIMARY]
مشكلي كه حالا پيش مي‌آيد اين است كه وقتي ميخواهيم فيلد Primary Key اين جدول را به يك Foreign Key جدول ديگري Ralation بدهيم، فيلد Primary Key به عنوان PK قابل استفاده نيست در صورتي كه در مشخصات جدول اين فيلد، يك PK است.
ممنون

[محمد سلیم آبادی]

یک جواب سریع می دهم،
اگر شما از unique constraint به جای primary key constraint استفاده کنید می توانید یک relation/link ایجاد کنید.

[محمد سلیم آبادی]

با سلام
مشكلي كه حالا پيش مي‌آيد اين است كه وقتي ميخواهيم فيلد Primary Key اين جدول را به يك Foreign Key جدول ديگري Ralation بدهيم، فيلد Primary Key به عنوان PK قابل استفاده نيست در صورتي كه در مشخصات جدول اين فيلد، يك PK است.
ممنون

امکان دارد که متن ممانعت از ایجاد relationshipls را ارسال کنید؟

شاید نوع داده های دو ستون (یا ستون ها در موارد composite) یکسان نیست.

در هر صورت اگر تنها برای relation ایجاد کردن می خواهید ستون محاسباتی را تبدیل PK کنید من به شما پیشنهاد می کنم یک ستون مجزا برای PK طراحی کنید و ستون محاسباتی را تبدیل به Unique Key کنید.
با این کار شما دیگر مشکل برقرار گردن اتصال نیز ندارید!

[A.Farzin]

1 - ساخت جدول PK

CREATE TABLE [dbo].[mytable](
[FK1] [int] NOT NULL,
[FK2] [int] NOT NULL,
[ID] AS ISNULL(([FK1]*100000)+[FK2], 0)
PRIMARY KEY CLUSTERED
)
2 - ساخت جدول FK

CREATE TABLE T1 (
col1 int NOT NULL,
IDFK int not null
)
3 - ايجاد Relationship

ALTER TABLE [dbo].[T1] WITH CHECK
ADD CONSTRAINT [FK_mytable_T1]
FOREIGN KEY([IDFK])
REFERENCES [dbo].[mytable] ([ID])

4 - پيغام خطا

Msg 1784, Level 16, State 1, Line 1
Cannot create the foreign key 'FK_mytable_T1' because the referenced column 'mytable.ID' is a computed column.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

اشكال Relation را درست كردم به شكل بالا درآمد

[محمد سلیم آبادی]

در قسمت سوم (ایجاد relation) شما یک اشتباه کوچکی کرده اید که نام جدول FK را اشتباه نوشته اید.

من در SQL Server 2008 با این دستور کارم راه افتاد:


CREATE TABLE [dbo].[mytable]
(
[FK1] [int] NOT NULL,
[FK2] [int] NOT NULL,
[ID] AS ([FK1]*100000)+[FK2] persisted,
Unique([id])
)
Go

CREATE TABLE T1
(
col1 int NOT NULL,
IDFK int not null
)
Go

ALTER TABLE [dbo].[T1] WITH CHECK
ADD CONSTRAINT [FK_mytable_T1]
FOREIGN KEY([IDFK])
REFERENCES [dbo].[mytable] ([ID])


البته از آنجایی که SQL 2000 دستور PERSISTED را ندارد، من پیشنهاد می کنم از Computed Column صرف نظر کنید به جای آن از یک after inserted, update trigger ( اگر در SQL 2000 وجود داشته باشد) برای محاسبه ی این ستون استفاده کنید.

نکته ی بعدی این است که دقیقا به چه منظوری می خواهید این اتصال شکل بگیرد؟ تنها برای مقید کردن مقادیر ستون کلید خارجی؟ یعنی مقادیر ستون خارجی باید در مقادیر reference نیز وجود داشته باشند؟
اگر اینطور است می توانید از CHECK() Constraint based on UDF استفاده کنید.

[A.Farzin]

msalim جان،
بله، متشكرم. قيد استفاده از آن را در 2000 زدم.
از پاسخهاي مفيد شما متشكرم.

[AminSobati]

آقای فرزین متاسفانه من در حال حاضر به نسخه 2000 دسترسی ندارم. ولی آزمایش این ضرر نداره:
در نسخه 2000 ساخت ایندکس روی فیلد Computed تقریبا تاثیر Persisted رو داره. شاید در این حالت بشه FK رو ایجاد کرد

[محمد سلیم آبادی]

آقای AminSobati عزیز، اگر به پیغام خطایی که در پست شماره 8 قرار داده شده است دقت کنید در نسخه ی 2000 از computed column اصلا نمی توانیم به عنوان یک reference در یک foreign key constraint استفاده کنیم.
و حتی اگر آن ستون هم تبدیل به primary key constraint یا unique constraint هم بکونیم فرقی نخواهد داشت. (در واقع تا آنجایی که می دانم تنها موقعی می توانیم در یک relation از ستونی به عنوان مرجع استفاده کنیم که ستون مرجع یک PK یا یک Unique Constraint باشد)
و از طرفی computed Column بدون استفاده از کلید واژه ی PERSISTED به عنوان ستون مجازی یا Virtual Column شناخته می شود و هر با که در یک query به این ستون رجوع می شود در همان لحظه محاسبه انجام شده و در نتیجه این ستون هیچ فضای فیزیکی برای ذخیره شدن اشغال نمی کند. و از آنجایی که پیداست در نسخه ی 2000 این کلید واژه وجود خارجی ندارد. در نتیجه امکان ساخت relation بر پایه ی این ستون نیز وجود نخواهد داشت!

[AminSobati]

در نسخه 2000 میتونین فیلد Computed رو بدون Persisted هم از حالت مجازی خارج کنید:



set statistics profile on
go

create table p(
c1 int,
c2 int,
c3 as c1+c2)
go

select * from p
go

create index ix1 on p(c3,c2,c1)
go

select * from p


Plan کوئری اول، دو سطر Compute Scalar نشون میده. یکیش برای محاسبه ستون c3 و یکی برای نیاز داخلی خود Query Processor. بعد از ساخت ایندکس، کوئری دوم دیگه c3 رو محاسبه نمیکنه چون مقادیرش بصورت فیزیکی در ایندکس ذخیره شدن.
اما متاسفانه همونطور که اشاره کردید اجازه ساخت FK رو نمیده.