Sql Constraint Örnek #2

If Not Exists(Select * From sysdatabases Where name='NesneAmbari')
    Create Database NesneAmbari
Go
Use NesneAmbari
Go
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='Universite')
    Create Table Universite
    (
        UniversiteId Int Identity,
        UniversiteAdi Varchar(75) Not Null,
        UniversiteAdresi Varchar(150) Not Null,
        UniversiteTel Varchar(15) Not Null,
        UniversiteFax Varchar(15) Not Null,
        UniversiteWebSitesi Varchar(75) Not Null,
        Constraint PK_Universite_UniversiteId Primary Key(UniversiteId),
        Constraint AN_Universite_UniversiteAdi Unique(UniversiteAdi)
    )
Go
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='Feym')
    Create Table Feym
    (
        FeymId Int Identity,
        FeymAdi Varchar(75) Not Null,
        FeymAdresi Varchar(150) Not Null,
        UniversiteId Int,
        Constraint PK_Feym_FeymId Primary Key(FeymId),
        Constraint FK_Feym_UniversiteId Foreign Key(UniversiteId)
        References Universite(UniversiteId) On  Delete Cascade
    )
Go
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='Bolum')
    Create Table Bolum
    (
        BolumId Int Identity,
        BolumAdi Varchar(75) Not Null,
        FeymId Int,
        Constraint PK_Bolum_BolumId Primary Key(BolumId),
        Constraint FK_Bolum_FeymId Foreign Key(FeymId)
        References Feym(FeymId) On Delete Cascade
    )
Go
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='Ders')
    Create Table Ders
    (
        DersId Int Identity,
        DersAdi Varchar(75)
        Constraint PK_Ders_DersId Primary Key(DersId)
    )
Go
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='AkademikDonemler')
    Create Table AkademikDonemler
    (
        DonemId Int Identity,
        DonemAdi Varchar(50) NOT NULL,
        Constraint PK_AkademikDonemler_DonemId Primary Key(DonemId)
    )
Go
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='KullaniciTipleri')
    Create Table KullaniciTipleri
    (
        TipId Int Identity,
        TipAdi Varchar(50) Not Null,
        Constraint PK_KullaniciTipleri_TipId Primary Key(TipId)
    )
Go
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='Kullanicilar')
    Create Table Kullanicilar
    (
        KullaniciId Int IDentity,
        KullaniciAdi Varchar(75) Not Null,
        Sifre Varchar(15) Not Null,
        Email Varchar(15) Not Null,
        OnayDurumu Bit Default 0,
        TipId Int,
        Constraint PK_Kullanicilar_KullaniciId Primary Key(KullaniciId),
        Constraint AN_Kullanicilar_KullaniciAdi Unique(KullaniciAdi),
        Constraint AN_Kullanicilar_Email Unique(Email),
        Constraint FK_Kullanicilar_TipId Foreign Key(TipId)
        References KullaniciTipleri(TipId)
    )
Go
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='DersSorumlusu')
    Create Table DersSorumlusu
    (
        SorumluId Int,
        DersId Int,
        DonemId Int,
        Constraint PK_DersSorumlusu_PK Primary Key(SorumluId,DersId,DonemId),
        Constraint FK_DersSorumlusu_SorumluId Foreign Key(SorumluId)
        References Kullanicilar(KullaniciId) On Delete Cascade,
        Constraint FK_DersSorumlusu_DersId Foreign Key(DersId)
        References Ders(DersId) On Delete Cascade,
        Constraint FK_DersSorumlusu_DonemId Foreign Key(DonemId)
        References AkademikDonemler(DonemId) On Delete Cascade
    )
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='DersMateryali')
    Create Table DersMateryali
    (
        MateryalId Int Identity,
        MateryalAdi Varchar(75) Not Null,
        MateryalKonusu Varchar(200) Not Null,
        YuklenmeTarihi Datetime,
        OnayDurumu Bit Default 0,
        DonemId Int,
        DersId Int,
        Constraint PK_DersMateryali_MateryalId Primary Key(MateryalId),
        Constraint FK_DersMateryali_DonemId Foreign Key(DonemId)
        References AkademikDonemler(DonemId),
        Constraint FK_DersMateryali_DersId Foreign Key(DersId)
        References Ders(DersId) On Delete Cascade
    )
If Not Exists (Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME='BolumDersleri')
    Create Table BolumDersleri
    (
        DersId Int,
        SorumluId Int,
        DonemId Int,
        BolumId Int,
        Constraint FK_BolumDersleri_DersId Foreign Key(DersId)
        References Ders(DersId),
        Constraint FK_BolumDersleri_SorumluId Foreign Key(SorumluId)
        References Kullanicilar(KullaniciId),
        Constraint FK_BolumDersleri_DonemId Foreign Key(DonemId)
        References AkademikDonemler(DonemId),
        Constraint FK_BolumDersleri_BolumId Foreign Key(BolumId)
        References Bolum(BolumId)
    )