欢迎来到忆约科技! 手机版

C# 社区推荐

当前位置:首页 > 编程专栏 > SQL Server > 正文

在SQL Server数据库中PK,UK, DF, CK, FK是什么意思?

编辑时间:2016年06月19日 14:30 作者:小编

创建任意数据库,执行以下测试sql脚本

USE TestDB
GO

--PK, 主键, {PK_Table}
CREATE TABLE [Users](
		[Id] [int] IDENTITY (1,1)
			CONSTRAINT [PK_Users] PRIMARY KEY([Id]),
		[Name] [nvarchar](255)
);
GO
--INSERT INTO [Users]([Name])VALUES('demo user');
--SELECT * FROM [Users];


--UK, 唯一约束, {UK_Table_Column}
CREATE TABLE [Users2](
		[Id] [int] IDENTITY (1,1)
			CONSTRAINT [PK_Users2] 
			PRIMARY KEY([Id]),
		[Name] [nvarchar](255)
			CONSTRAINT [UK_Users2_Name] 
			UNIQUE ([Name]),
);
--INSERT INTO [Users2]([Name])VALUES('demo user');
----再次创建违反唯一约束
--INSERT INTO [Users2]([Name])VALUES('demo user');
--SELECT * FROM [Users2];


--DF, 默认约束, {DF_Table_Column}
CREATE TABLE [Users3](
		[Id] [int] IDENTITY (1,1)
			CONSTRAINT [PK_Users3] 
			PRIMARY KEY([Id]),
		[Name] [nvarchar](255),
		[Sex] [int]
			CONSTRAINT [DF_Users3_Sex]
			DEFAULT 1,
);
----自动填充字段值, Sex
--INSERT INTO [Users3]([Name])VALUES('demo user');
--SELECT * FROM [Users3];


--CK, 检查约束, {CK_Table_Column}
CREATE TABLE [Assets](
		[Id] [int] IDENTITY (1,1)
			CONSTRAINT [PK_Assets] 
			PRIMARY KEY([Id]),
		[Balance] [decimal](20, 4)			
			CONSTRAINT [CK_Assets_Balance] 
			CHECK([Balance]>=0),
);
GO
----[Balance]小于0则违反约束条件
--INSERT INTO [Assets]([Balance])VALUES(-1);
--INSERT INTO [Assets]([Balance])VALUES(2);
--SELECT * FROM [Assets];


--FK, 主外键关系, {CK_SourceTable_Table_Column}
CREATE TABLE [Article](
		[Id] [int] IDENTITY (1,1)
			CONSTRAINT [PK_Article] 
			PRIMARY KEY([Id]),
		[UserId] [int] NOT NULL
			CONSTRAINT [FK_Users_Article_UserId] 
			FOREIGN KEY REFERENCES [Users]([Id]),
		[Content] [text]
);
GO
--INSERT INTO [Article]([UserId],[Content])VALUES(1,'内容');
--SELECT * FROM [Article];

二维码