ä¸ã主é®æ¯è½ç¡®å®ä¸æ¡è®°å½çå¯ä¸æ è¯ï¼æ¯å¦ï¼ä¸æ¡è®°å½å
æ¬èº«ä»½æ£å·ï¼å§åï¼å¹´é¾ã身份è¯å·æ¯å¯ä¸è½ç¡®å®ä½ è¿ä¸ªäººçï¼å
¶ä»é½å¯è½æéå¤ï¼æ以ï¼èº«ä»½è¯å·æ¯ä¸»é®ã
å¤é®ç¨äºä¸å¦ä¸å¼ 表çå
³èãæ¯è½ç¡®å®å¦ä¸å¼ 表记å½çå段ï¼ç¨äºä¿ææ°æ®çä¸è´æ§ãæ¯å¦ï¼A表ä¸çä¸ä¸ªå段ï¼æ¯B表ç主é®ï¼é£ä»å°±å¯ä»¥æ¯A表çå¤é®ã
主é®åå¤é®çº¦æçæ¹æ³ï¼
create table Student --å»ºè¡¨æ ¼å¼:create table èªå®ä¹ç表å
( --å段åä¸è¬ä¸ºæä¸å®æä¹çè±æ
StudentName nvarchar(15), -- æ ¼å¼ï¼å段åç±»å()æ¬å·éé¢çæ¯å
许è¾å
¥çé¿åº¦
StudentAge int, --intåçåé¢ä¸éè¦æ¥é¿åº¦
StudentSex nvarchar(2) --æåä¸ä¸ªå段åé¢ä¸è¦éå·
)
--å¨å建表æ¶å°±å¯ä»¥å¯¹å段å ä¸çº¦æ:
create table Student
(
StudentNo int PRIMARY KEY IDENTITY(1,1), --å 主é®çº¦æ,è¿ææ è¯åå±æ§ï¼ä¸¤è
ææå®ä½å®æ´æ§ï¼
StudentName nvarchar(15) not null, --å é空约æ,ä¸å "not null" é»è®¤ä¸ºï¼å¯ä»¥ä¸ºç©º
StudentSchool text(20) FOREIGN KEY REFERENCES SchoolTable(SchoolName), --å å¤é®çº¦æ,æ ¼å¼:FOREIGN KEY REFERENCES å
³èç表å(å段å)
StudentAge int DEFAULT ((0)), --å é»è®¤å¼çº¦æ
StudentSex nvarchar(2) CHECK(StudentSex=N'ç·' or StudentSex=N'女') --å æ£æ¥çº¦æ,æ ¼å¼:check (æ¡ä»¶è¡¨è¾¾å¼)
)
--å¦æå¨è¡¨å建好äºä»¥ååå 约æï¼åæ ¼å¼åå«ä¸ºï¼
-- 主é®:
alter table 表å
add constraint PK_å段å--"PK"为主é®ç缩åï¼å段å为è¦å¨å
¶ä¸å建主é®çå段å,'PK_å段å'就为约æå
primary key (å段å) --å段åå
--å¯ä¸çº¦æ:
alter table 表å
add constraint UQ_å段å
unique (å段å
--å¤é®çº¦æï¼
alter table 表å
add constraint FK_å段å--"FK"为å¤é®ç缩å
foreign key (å段å) references å
³èç表å(å
³èçå段å) --注æ'å
³èç表å'å'å
³èçå段å'
alter table 表A add constraint FK_B foreign key (ticket_no) references 表B(ticket_no)
alter table 表A add constraint FK_C foreign key (person_no) references 表C(person_no)
alter table æ绩表 add constraint FK_StudentNo foreign key (StudentNo) references Student(StudentNo)
ON UPDATE CASCADE ON DELETE CASCADE
级èæ´æ°ï¼çº§èå é¤ï¼è¿æ ·å¨å é¤ä¸»è¡¨Studentæ¶ï¼æ绩表ä¸è¯¥å¦ççæææ绩é½ä¼å é¤ã
--æ£æ¥çº¦æ:
alter table 表å
add constraint CK_å段å
check (æ¡ä»¶è¡¨è¾¾å¼) --æ¡ä»¶è¡¨è¾¾å¼ä¸çæ¡ä»¶ç¨å
³ç³»è¿ç®ç¬¦è¿æ¥
--é»è®¤å¼çº¦æ:
alter table 表å
add constraint DF_å段å
default 'é»è®¤å¼' for å段å--å
¶ä¸ç'é»è®¤å¼'ä¸ºä½ æ³è¦é»è®¤çå¼ï¼æ³¨æ'for'
--å é¤å建ç约æ:
alter table 表å
drop constraint 约æå--约æåä¸ºä½ åé¢å建çå¦ï¼PK_å段è¿æ ·ç约æå
--注æï¼å¦æ约ææ¯å¨å建表çæ¶åå建çï¼åä¸è½ç¨å½ä»¤å é¤
--åªè½å¨'ä¼ä¸ç®¡çå¨'éé¢å é¤
-- è·åSqlServerä¸è¡¨ç»æ
SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
syscolumns.length
FROM syscolumns,systypes
WHERE syscolumns.xusertype = systypes.xusertype
AND syscolumns.id = OBJECT_ID('Student')
-- åç¬æ¥è¯¢è¡¨éå¢å段
SELECT [name] FROM syscolumns WHERE
id = OBJECT_ID(N'Student') AND COLUMNPROPERTY(id,name,'IsIdentity')=1
-- è·å表主å¤é®çº¦æ
EXEC sp_helpconstraint 'StuResults'
-- æ¥è¯¢è¡¨ä¸»é®å¤é®ä¿¡æ¯
SELECT sysobjects.id objectId,OBJECT_NAME(sysobjects.parent_obj) tableName,
sysobjects.name constraintName, sysobjects.xtype AS constraintType,
syscolumns.name AS columnName
FROM sysobjects INNER JOIN sysconstraints
ON sysobjects.xtype in('C', 'F', 'PK', 'UQ', 'D')
AND sysobjects.id = sysconstraints.constid
LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id
WHERE OBJECT_NAME(sysobjects.parent_obj)='StuResults'
温馨提示:答案为网友推荐,仅供参考