Constraints(Kısıtlamalar) kullanma oluşturma ve anlama

Merhaba;

Constraintlere örnek vermek gerekirse aşağıdakileri sıralayabiliriz.Bunların dışında kalanlarda vardır örneğin tablo oluştururken null value kabul etmesi gerektiğini işaretleriz aslında buda bir constraint’dir ama ben daha çok aşağıdakiler üzerinde duracağım.

PRIMARY KEY Constraints

FOREIGN KEY Constraints

UNIQUE Constraints

CHECK Constraints

ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(UniqueColumnName)

ALTER TABLE Department ADD CONSTRAINT UNIQUE_DepartmentName UNIQUE (NAME)

ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE

(UniqueColumnName1,UniqueColumnName2)

ALTER TABLE Department ADD CONSTRAINT UNIQUE_DepartmentNameRoom

UNIQUE(NAME,RoomNumber)

———— DEFAULT ————

create table NewCustomers(

CustomerID int not null IDENTITY(1,1) PRIMARY KEY,

CustomerName varchar(30) not null

CONSTRAINT DF_CustName DEFAULT ‘To be entered’,

City varchar(30) DEFAULT ‘Hatay’,

InsertionDate smalldatetime DEFAULT Getdate(),

CreatedbyUser nvarchar(128) DEFAULT System_User)

GO

INSERT INTO NewCustomers(CustomerName,City)

VALUES(‘Hasan Mansur’,’Istanbul’)

INSERT INTO NewCustomers(CustomerName)

VALUES (‘Songül Mansur’)

SELECT * from NewCustomers

INSERT INTO NewCustomers(CustomerName,City)

VALUES (‘Figen Mansur’,DEFAULT)

INSERT NewCustomers

DEFAULT VALUES

———– FOREIGN KEY ————

create table Customers(

CustomerID int PRIMARY KEY,

CustomerName varchar(20) not null)

Create table Orders(

OrderID int IDENTITY(1,1) PRIMARY KEY,

CustomerID int not null,

OrderDate smalldatetime not null DEFAULT CURRENT_TIMESTAMP)

ALTER TABLE Orders

ADD CONSTRAINT FK_Orders

FOREIGN KEY (CustomerID)

REFERENCES Customers(CustomerID)

ON DELETE CASCADE

INSERT Customers

VALUES (1,’Hasan Mansur’)

GO

INSERT Customers

VALUES (2,’Figen Mansur’)

GO

INSERT Customers

VALUES (3,’Songül Mansur’)

GO

INSERT Orders (CustomerID)

VALUES(1)

GO

INSERT Orders (CustomerID)

VALUES(1)

GO

INSERT Orders (CustomerID)

VALUES(2)

GO

INSERT Orders (CustomerID)

VALUES(2)

GO

INSERT Orders (CustomerID)

VALUES(2)

GO

INSERT Orders (CustomerID)

VALUES(3)

GO

INSERT Orders (CustomerID)

VALUES(3)

GO

PRINT CHAR(10) + ‘Original Customers Table’ + CHAR(10)

SELECT * FROM Customers

PRINT CHAR(10) + ‘Original Orders Table’ + CHAR(10)

SELECT * FROM Orders

DELETE Customers

where customerID=2

go

PRINT CHAR(10) + ‘Original Customers Table’ + CHAR(10)

SELECT * FROM Customers

PRINT CHAR(10) + ‘Original Orders Table’ + CHAR(10)

SELECT * FROM Orders

drop table orders

go

drop table customers

go

alter table orders

drop constraint FK_Orders

go

drop table orders

go

drop table customers

go

————- —————

Constraints(Kısıtlamalar) kullanma oluşturma ve anlama