Özgür Özvarış

Bir yazılımcının kırık dökük dünyası

Msql foreign key arka planı

clock Haziran 28, 2016 17:11 by author OzgurOzvaris

Merhaba ,

Bu günki yazımızda mssql de foreign key ile ilgili bir kaç script paylayacağım. Öncelikle bu konu nerden çıktı diye düşünebilirsiniz çıkış noktası entityframework (EF) te (Database First) alter table ile değişiklik yapılan bir tablonun EF foreign key (FK) bağlantıları uçtu. Neden uçtuğunu anlamak için EF kün tablo eklerken kullandığı sql cümlelerini  profiler aracalığı ile  incelemek ve süzerek boş dönen sql cümlesini tespit etmek durumuda kaldım. Baştan söyleyim çok zor bir yöntem oldu ancak sonuçta sorunu bulmamı sağladı.

Baştan başlar isek öncelilkle birbiri ile foreign key bağlantısı kuracağımız iki tablo açalım

CREATE TABLE Table_Ttmp 

(
Table_T_ID numeric(12, 0) IDENTITY(1,1),
PRIMARY KEY (Table_T_ID),


)

CREATE TABLE Table_Mtmp 

(
Table_M_ID numeric(12, 0) IDENTITY(1,1),
Table_T_ID numeric(12, 0) NOT NULL,
PRIMARY KEY (Table_M_ID),
--FOREIGN KEY (Table_T_ID) REFERENCES Table_Ttmp ,  

)

Bir foreign key constraint eklemek için standart sql cümlesini çalıştırarak devam edelim. (Yukarıdaki scriptte aslında FK oluşturma ile ilgili satır commentli. Bu satırıda aynı işlemi yapar.)

ALTER TABLE dbo.Table_Mtmp ADD CONSTRAINT
FK_Table_Mtmp_Table_Ttmp FOREIGN KEY
(
Table_M_ID
) REFERENCES dbo.Table_Ttmp
(
Table_T_ID
) ON UPDATE  NO ACTION 
 ON DELETE  NO ACTION

Üçüncü bir FK oluşturma yöntemide Sql Server Management Studio (SQLMS) aracılığı ile tabloda design diyerek ve Relations ları seçerek FK oluşturmak. Burada kaydet demeden oluşturulan scripte bakarsanız yukarıda ki scriptin aynısını oluşturduğunu görecektsiniz

Yukarıdaki üç yöntemden herhabgi biri  ile FK ilişkilsi eklediğinizde MSSQL iki tablo arasında ilişki kuruyor. Yukarıdaki tabloda anlaşıldığı üzere ilişki Table_Mtmp ile Table_Ttmp arasıdına oluşturulmuş durumda. Table_Ttmp Primary key'in olduğu tablo, Table_Mtmp foreign key'in olduğu tablo.

Buraya kadar anlattıklarımız giriş şimdi öğreneceklerimizin alt yapısını oluşturmak içinde. SQLMS ekranlarıyla geldiğimiz aşamayı görelim. 

 

 

Şimdi asıl paylaşacağım aşamalara geldik.

 

SQLMS yukarıdaki bağlantıyı göstebilmek için şimdi paylaştığım sql cümlesini kullanıyor. Bu scriptin düzgün sonuç vermesi için use ile db niniz seçmeniz gerekiyor...

select Fk.name, Fk.object_id, Fk.is_disabled, Fk.is_not_for_replication, Fk.delete_referential_action, Fk.update_referential_action, 
object_name(Fk.parent_object_id) as Fk_table_name, schema_name(Fk.schema_id) as Fk_table_schema, TbR.name as Pk_table_name, 
schema_name(TbR.schema_id) Pk_table_schema, col_name(Fk.parent_object_id, Fk_Cl.parent_column_id) as Fk_col_name, 
col_name(Fk.referenced_object_id, Fk_Cl.referenced_column_id) as Pk_col_name, Fk_Cl.constraint_column_id, Fk.is_not_trusted 
from sys.foreign_keys Fk 
left outer join sys.tables TbR on TbR.object_id = Fk.referenced_object_id 
inner join sys.foreign_key_columns Fk_Cl on Fk_Cl.constraint_object_id = Fk.object_id 

where	Fk.parent_object_id = object_id(N'dbo.Table_Mtmp') or Fk.referenced_object_id = object_id(N'dbo.Table_Mtmp')

 

EF ise bu ilişkiyi almak için başka bir sql script kullanıyor. EF Update Database Model dediğinizde ve yeni bir tablo eklemek istediğinizde yada bir tablodaki güncellemeyi almak için silip tekrar eklediğinizde uzunca birkaç sql cümlesi gönderiyor. Bunlardan birtanesi tablolar arasındaki ilişkiyi çekmek için. 359 satırlık bir sql cümlesi olduğu için onu dosya olarak paylaşıyorum. Bunu paylaşma nedenim bir kaynak olması için. Yoksa satır satır bu sql'in ne yaptığını bende bilmiyorum. Ancak biraz sonra açıklayacağım scriptler bu büyük scriptin içinden işlevselliklerine göre seçilerek elde edildi. 

 EFFullScript.txt (16,72 kb)

 

Bu uzunça scriptin çıktısı yukarıda paylaştığım ekran. Burda belirtilen bilgiler Table_Ttmp diye bir tablonun Table_T_ID primary key alnı ile Table_Mtmp tablosunun Table_M_ID ( Doğru bağlantı tabiki Table_T_ID olması gerekiyordu ama test tablosu olduğu için düzeltmedim) kolonu arasında FK_Table_Mtmp2_Table_T isimli (yukarıda SQLMS da aynı isim var)  [dbo][FK_Table_Mtmp2_Table_T] id li (Bu id name'in DB tarafındaki karşılığı) bir ilişki vardır. Bu satır boş gelirse EF association Model de FK ilişkiyi kuramıyor. Bizde de aynen böyle oldu ve FK SQLMS da gözüktüğü halde EF de gözükmüyordu. Bu nedenle sorunu bulmak için bu uzunca scripti incelemeye almak durumunda kaldık. Buda bizi SQL server FK mantığının derinlerine doğru bir yolculuğa çıkardı. Merak eden buradan sonraki aşamayı takip ederek bu derinliğe bizle birlikte gelebilir.

Öncelikle kısaca fikir edinmek için bu uzun sql cümlesinden birkaç kelime edeyim. Bu sql cümlesinin aslında ilk 358 satırı sabit 359. satır yani "WHERE" ile başlayan satır modelinizdeki tablolara göre dinamik olarak tablo filitreleri oluşturuyor. Kodun daha iyi anlaşılabilmesi için 359. satırdan parametre satırını 360'a valularınıda 361'i satırdan itibaren tablo lara göre enterlı olarak yazdım. Şimdi 361. satırı paylaşıyorum.

@p0=N'K14_abc',@p1=N'dbo',@p2=N'Table_Mtmp',
@p3=N'K14_abc',@p4=N'dbo',@p5=N'Table_Ttmp',
@p6=N'K14_abc',@p7=N'dbo',@p8=N'Table_Mtmp',
@p9=N'K14_abc',@p10=N'dbo',@p11=N'Table_Ttmp'

Görüldüğü gibi burada modele eklediğim başta oluşturduğumuz  Table_Mtmp ve Table_Ttmp satırlarımıza ait tekrar etmiş 4 satır mevcut. Eğer buraya bir 3. tablo daha ekleseydik 360. satırdaki where bölümüne ve parametre value bölümüne bu tablolarda eklenmiş olacaktı ancak ana sql cümlesi değişmeyecekti.  Şimdi gelelim bizim tablolarımızdaki ilişkileri alan sql parçasına. Bu aşamadan sonra paylaşacağım sql scriptler bu 359 satırlık bölümden olacak.

Öncelikle iki tablo arasında FK yı ve FK ilişkisinin temel bilgilerini gösteren ve bu ilişkinin "id" değerini üreten parçayı gösterelim. Bu id bir sonraki script'te ilişkinin detaylarını görmemiz için kullanılacak. Son kısımdaki tablo isimleri parametrelerini değiştirerek bu sorguları istediğiniz  tablo için çalıştırabilirsiniz.

SELECT 
--[Join5].[Ordinal] AS [C1], 
--[UnionAll4].[CatalogName] AS [C2], 
--[UnionAll4].[SchemaName] AS [C3], 
--[UnionAll4].[Name] AS [C4], 
--[Join5].[Name1] AS [C5], 
[UnionAll5].[CatalogName] AS [C6], 
[UnionAll5].[SchemaName] AS [C7], 
[UnionAll5].[Name] AS [C8], 
--[Join5].[Name2] AS [C9], 
[Extent2].[Name] AS [Name], 
[Extent1].[Id] AS [Id]--, 
--CASE WHEN ([Extent1].[DeleteRule] = 'CASCADE') THEN cast(1 as bit) WHEN ([Extent1].[DeleteRule] <> 'CASCADE') THEN cast(0 as bit) END AS [C10]
FROM 

	(SELECT
			quotename(rc.CONSTRAINT_SCHEMA) + quotename(rc.CONSTRAINT_NAME) [Id]
			, CAST(rc.UPDATE_RULE as nvarchar(11)) [UpdateRule]
			, CAST(rc.DELETE_RULE as nvarchar(11)) [DeleteRule]
			FROM
			INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
		  ) AS [Extent1]
	INNER JOIN (
			SELECT
			quotename(tc.CONSTRAINT_SCHEMA) + quotename(tc.CONSTRAINT_NAME) [Id]
			, quotename(tc.TABLE_SCHEMA) + quotename(tc.TABLE_NAME) [ParentId]
			,   tc.CONSTRAINT_NAME [Name]
			,   tc.CONSTRAINT_TYPE [ConstraintType]
			,   CAST(CASE tc.IS_DEFERRABLE WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsDeferrable]
			,   CAST(CASE tc.INITIALLY_DEFERRED WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsInitiallyDeferred]
			FROM
			INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
			WHERE tc.TABLE_NAME IS NOT NULL
		  ) AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]


    
	LEFT OUTER JOIN        
	(SELECT 
		3 AS [C1], 
		[Extent13].[Id] AS [Id], 
		[Extent13].[Name] AS [Name], 
		[Extent13].[CatalogName] AS [CatalogName], 
		[Extent13].[SchemaName] AS [SchemaName]
		FROM (
			SELECT
			quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
			,   TABLE_CATALOG [CatalogName]
			,   TABLE_SCHEMA [SchemaName]
			,   TABLE_NAME    [Name]
			FROM
			INFORMATION_SCHEMA.TABLES
			WHERE
			TABLE_TYPE = 'BASE TABLE'
		  ) AS [Extent13]
	UNION ALL
		SELECT 
		12 AS [C1], 
		[Extent14].[Id] AS [Id], 
		[Extent14].[Name] AS [Name], 
		[Extent14].[CatalogName] AS [CatalogName], 
		[Extent14].[SchemaName] AS [SchemaName]
		FROM (
			SELECT
			quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
			,   TABLE_CATALOG             [CatalogName]
			,   TABLE_SCHEMA              [SchemaName]
			,   TABLE_NAME                [Name]
			,   VIEW_DEFINITION           [ViewDefinition]
			,   CAST( CASE IS_UPDATABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsUpdatable]
			FROM
			INFORMATION_SCHEMA.VIEWS
			WHERE
			NOT (TABLE_SCHEMA = 'dbo'
			AND TABLE_NAME in('syssegments', 'sysconstraints')
			AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
		  ) AS [Extent14]) AS [UnionAll5] ON (3 = [UnionAll5].[C1]) AND ([Extent2].[ParentId] = [UnionAll5].[Id])
	WHERE 
	(
		
		(
			([UnionAll5].[CatalogName] LIKE '...') AND 
			([UnionAll5].[SchemaName] LIKE 'dbo') AND 
			([UnionAll5].[Name] LIKE 'Table_Mtmp')
		)

		OR 
		(
			([UnionAll5].[CatalogName] LIKE '...') AND 
			([UnionAll5].[SchemaName] LIKE 'dbo') AND 
			([UnionAll5].[Name] LIKE 'Table_Ttmp')
		)
	)

 

Bu scriptin çıktısı şu şekilde olmalı. Eğer Table_Ttmp ninde FK bağlantıları olsaydı onlarda gelecekti

 

 

Şimdi paylaşacaım script ise bu id değerine göre iki tablo arasında hangi kolonlar arasında bu ilişkinin olduğunu gösteriyor

SELECT
[Extent3].[Ordinal] AS [Ordinal], 
0 AS [C1], 
[Extent3].[ConstraintId] AS [ConstraintId], 
6 AS [C2], 
[Extent3].[FromColumnId] AS [FromColumnId], 
6 AS [C3], 
[Extent3].[ToColumnId] AS [ToColumnId]
FROM (
  SELECT
  quotename(FC.CONSTRAINT_SCHEMA) + quotename(FC.CONSTRAINT_NAME) + quotename(cast(FC.ORDINAL_POSITION as nvarchar(30))) [Id]
  ,   quotename(PC.TABLE_SCHEMA) + quotename(PC.TABLE_NAME) + quotename(PC.COLUMN_NAME) [ToColumnId]
  ,   quotename(FC.TABLE_SCHEMA) + quotename(FC.TABLE_NAME) + quotename(FC.COLUMN_NAME) [FromColumnId]
  ,   quotename(FC.CONSTRAINT_SCHEMA) + quotename(FC.CONSTRAINT_NAME) [ConstraintId]
  ,   FC.ORDINAL_POSITION [Ordinal]
  FROM 
	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 
	  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PC /* PRIMARY KEY COLS*/ ON       
		RC.UNIQUE_CONSTRAINT_SCHEMA = PC.CONSTRAINT_SCHEMA  
		AND      RC.UNIQUE_CONSTRAINT_NAME    = PC.CONSTRAINT_NAME
      INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FC /* FOREIGN KEY COLS*/ ON       
		RC.CONSTRAINT_SCHEMA = FC.CONSTRAINT_SCHEMA         
		AND      RC.CONSTRAINT_NAME    = FC.CONSTRAINT_NAME 
		AND   PC.ORDINAL_POSITION = FC.ORDINAL_POSITION
) AS [Extent3]

where ('[dbo][FK_Table_Mtmp2_Table_T]' = [ConstraintId])

 

Çıktısı şu şekilde olmalı

 

 

Yukarıdaki çıktıdan anlaşıldığı üzere FromColumnId de gözüken Table_Mtmp tablosunun Table_M_ID kolonu ile ToColumnId de gözüken Table_Ttmp ve Table_T_ID arasında [dbo][FK_Table_Mtmp2_Table_T] idli bir bağlantı bulunmaktadır. Eğer bu satır boş gelirse EF FK ilişkisini (association) kuramıyor.

Aslında amacımıza ulaştık iki tablo arasında ilişkiyi gördük. Ama asıl çözüm sağlayan adım burada başlıyor. Burada satır boş gelirse sorunu nasıl anlayacağız. Çünkü bizde ilişki olduğu halde satır burada boş geliyordu. İşte onun tahlilinide bu script içinden aldığım son script ile paylaşarak yapacağız.

Select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where '[dbo][FK_Table_Mtmp2_Table_T]' =  quotename(CONSTRAINT_SCHEMA) + quotename(CONSTRAINT_NAME) 
Select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where CONSTRAINT_SCHEMA = 'dbo' AND CONSTRAINT_NAME = 'FK_Table_Mtmp2_Table_T'
Select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA = 'dbo' AND CONSTRAINT_NAME = 'PK__Table_Tt__96E7C6DB2B803210'

Ekran Çıktısı

  

Burada artık bu FK ilişkisinin hangi Constraint Name ler ile hangi tablolar arasında kurulduğu net olarak gözüküyor. Scripteki birinci tablo FK sahibi olan Table_Mtmp tablosun ait, ikinci satır PK (Primary key) tablosu ile ilikiliyi kurulan PK ya ait contraint'in isminin saklandığı UNIQUE_CONSTRAINT_NAME kolonunu içeriyor. Üçüncü satır aslında birinci satırla aynı KEY_COLUMN_USAGE Information Schemasına bakıyor ancak artık PK ya ait olan constraint name le bakıyor.

Bizim sorun neydi bari onuda söyliyeyim :) İşte 2 Satırda aldığımız  UNIQUE_CONSTRAINT_NAME PK Contraint name i üçüncü satırda where cümlesine konulunca boş geliyordu. Sebebi meğerse aynı PK değeri üzerinde aynı özelliklere sahip bir index tanımlanmış ve bağlantı PK Contraint Key'i ile değilde bu index ile kurulmuş. O index ise  KEY_COLUMN_USAGE Information Schemasında gözükmediği için EF FK ilişkisini kuramıyordu. O index'i sildik sorun çözüldü. Artık son scriptteki 3 satırda dolu gelmeye başladı :)

 

İyi çalışmalar.



Windows 7 80 nolu port listening stop etme PID 4 problemi

clock Haziran 8, 2016 16:42 by author OzgurOzvaris

Merhaba,

kaynaklar

http://stackoverflow.com/questions/1430141/port-80-is-being-used-by-system-pid-4-what-is-that

https://www.devside.net/wamp-server/opening-up-port-80-for-apache-to-use-on-windows 

Öcelikle IIS ve Servislerde IIS Admin Servicesi kapatmanız gerekmektedir.

Daha sonra varsa skype

daha sonra Winmdows Remote management servisi

daha sonra Web Deployment Agent Service

Windows 7 de iis'i kapasanızda 80 nolu portun dinlenmeye devam ederse.

Bu portu kimin dinlediğini görmek için öncelikle bunun için aşağıdaki nestat komut setinin kullanmanız gerekiyor.

netstat -o -n -a | findstr 0.0.0.0:80 

Eğer satır geliyor son kolonda 4 gözüküyorsa

İyi çalışmalar.

 



Hakkımızda  AboneOl 

Blog Yayınımıza Hoşgeldiniz.

Month List

RecentPosts

Sign In