SQL-Server/SQL_Constraint.sql at main · AllThingsDataWithAngelina/SQL-Server · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
---METHOD 1---
---Add Constraint with Alter Table Statement

--Primary Key
ALTER TABLE [dbo].[Customer]
ADD CONSTRAINT PK_Customer Primary Key ([CustomerID])

ALTER TABLE [dbo].[Customer]
Alter Column CustomerID int not null


--Unique Key
ALTER TABLE [dbo].[Customer]
Add Unique ([FirstName])


--Foreign Key

---First Add ProductID to Customer Table

ALTER TABLE [dbo].[Customer]
Add ProductID int not null


--Now change ProductID to Primary key by going through these two steps
ALTER TABLE [dbo].[Product]
Alter Column ProductID int not null

ALTER TABLE [dbo].[Product]
Add Constraint PK_Product Primary Key (ProductID)


ALTER TABLE [dbo].[Customer]
Add Constraint FK_Customer_Product Foreign Key ([ProductID]) References [dbo].[Product] ([ProductID])


---METHOD 2---
---Create Table and Constrainst at the same time


DROP TABLE [dbo].[Product]

ALTER TABLE [dbo].[Customer]
Drop Constraint [FK_Customer_Product]  ---Drop PK

DROP TABLE [dbo].[Customer]


CREATE TABLE Customer
(
	CustomerID int Primary Key,
	ProductID int Unique,
	FirstName Varchar(50) NOT NULL,
	MiddleName Varchar(50) NULL,
	LastName Varchar(100) NOT NULL,
	PhoneNumber Varchar(20) NOT NULL DEFAULT '000-000-0000'

)
DROP TABLE  Customer


CREATE TABLE Customer
(
	CustomerID int constraint PK_Customer Primary Key,
	ProductID int constraint UQ_Cust_Prod Unique,
	FirstName Varchar(50) NOT NULL,
	MiddleName Varchar(50) NULL,
	LastName Varchar(100) NOT NULL,
	PhoneNumber Varchar(20) NOT NULL DEFAULT '000-000-0000'

)

Insert into Customer (CustomerID, ProductID, FirstName, LastName)
values(1, 1, 'Angelina', 'Frimpong')

select * from Customer


---METHOD 2---
---Create Table and Constrainst at the same time

CREATE TABLE [dbo].[Product](
	[ProductID] int constraint PK_Product Primary Key,
	[ProductName] [varchar](50) NULL,
	[ProductNumber] [varchar](50) NULL,
	[ProductDescription] [varchar](150) NULL,
	[New_Used_Flag] int Default 0


)
GO


CREATE TABLE Customer
(
	CustomerID int not null,
	ProductID int not null,
	FirstName Varchar(50) NOT NULL,
	MiddleName Varchar(50) NULL,
	LastName Varchar(100) NOT NULL,
	PhoneNumber Varchar(20) NOT NULL DEFAULT '000-000-0000'

	constraint PK_Customer Primary Key (CustomerID),
	Constraint FK_Customer_Product Foreign Key (ProductID) References [dbo].[Product] (ProductID)

)


--
ALTER TABLE [dbo].[Product]
Add Constraint [CK_Product] CHECK ([ProductID] < 10)


insert into [dbo].[Product] ([ProductID], [ProductName])
values (9, 'Bicycle')


SELECT TOP (1000) [ProductID]
      ,[ProductName]
      ,[ProductNumber]
      ,[ProductDescription]
      ,[New_Used_Flag]
  FROM [TestDB].[dbo].[Product]