改变表已添加约束、外键

最新推荐文章于 2024-08-23 09:30:12 发布

原创最新推荐文章于 2024-08-23 09:30:12 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #jobs #go #date

SQL 专栏收录该内容

21 篇文章

订阅专栏

博客展示了两条SQL语句，第一条为employee表添加默认值约束和主键约束，对emp_id字段设置格式检查；第二条为employee表添加外键约束，job_id和pub_id字段分别引用jobs表和publishers表的对应字段。

ALTER TABLE [dbo].[employee] WITH NOCHECK ADD
CONSTRAINT [DF__employee__job_id__1B0907CE] DEFAULT (1) FOR [job_id],
CONSTRAINT [DF__employee__job_lv__1CF15040] DEFAULT (10) FOR [job_lvl],
CONSTRAINT [DF__employee__pub_id__1DE57479] DEFAULT ('9952') FOR [pub_id],
CONSTRAINT [DF__employee__hire_d__1FCDBCEB] DEFAULT (getdate()) FOR [hire_date],
CONSTRAINT [PK_emp_id] PRIMARY KEY NONCLUSTERED
(
[emp_id]
) ON [PRIMARY] ,
CONSTRAINT [CK_emp_id] CHECK ([emp_id] like '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or [emp_id] like '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
GO

ALTER TABLE [dbo].[employee] ADD
FOREIGN KEY
(
  [job_id]
) REFERENCES [dbo].[jobs] (
  [job_id]
),
FOREIGN KEY
(
  [pub_id]
) REFERENCES [dbo].[publishers] (
  [pub_id]
)
GO