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
————- —————

Arayan Bulur
Servislerimiz
İlginizi Çekebilecek Yazılar
Bunlar ilginizi çekebilir
Tema: Wordpress