如何建立主键、外键、索引、重建索引等

 1.如何通过sql语句获取一个表的主键字段和自增字段
-- drop table #temp
create table #temp(table_name varchar(100),field_name varchar(50),sign varchar(10),keys int)
insert into #temp
SELECT
--  表名=case when a.colorder=1 then d.name else '' end,
  表名=d.name,
  字段名=a.name,
  标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
  主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end
FROM syscolumns a
  inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
order by a.id,a.colorder

select * from #temp

select table_name,sum(keys)
from #temp
group by table_name
having  sum(keys)=0

select * from #temp
where table_name in(
 select table_name from #temp
 group by table_name
 having  sum(keys)=0)

2.查询表的外键

select b.name constname,c.name ForeignTable,d.name ForeignCol,
  e.name refTable,f.name refCol
from sysforeignkeys a
inner join sysobjects b  on a.constid=b.id
inner join sysobjects c  on a.fkeyid=c.id
inner join syscolumns d on a.fkeyid=d.id and a.fkey=d.colid
inner join sysobjects e  on a.rkeyid=e.id
inner join syscolumns f on a.rkeyid=f.id and a.rkey=f.colid

3.建表时，加主鍵、外鍵约束
CONSTRAINT [PK_dinner_request] PRIMARY KEY  CLUSTERED ( [in_id])  ON [PRIMARY]

CONSTRAINT [FK_Dinner_request_Ticket_no] FOREIGN KEY  ( ticket_no) REFERENCES [bread_ticket] ( ticket_no)

 

4.把一列改成主鍵列
alter table bread_ticket add constraint PK_bread_ticket_Ticket_no primary key  (ticket_no)

5.把兩列改成組合主鍵
alter table bank add constraint PK_bank primary key  (bank_type,bank_id)

6.新加唯一鍵
alter table person add constraint UK_person UNIQUE (identity_no)
alter table tbArea add constraint UK_tbArea UNIQUE (remark)

7.添加外鍵
alter table dinner_request add constraint FK_dinner_request_Ticket_no foreign key (ticket_no) references bread_ticket (ticket_no)

添加时不检查约束

alter table dbo.Lab_Profile
   WITH NOCHECK add constraint FK_Lab_Profile_ProfileID foreign key (ProfileID)
      references dbo.Profile (ID)
go

 

8.刪除主鍵
Alter table tabPiecePrice drop constrain PK_ tabPiecePrice
9.修改某一列加默認值
ALTER TABLE [dbo].[tabOA_Person] ADD
CONSTRAINT [DF_tabOA_Person_new_affiche] DEFAULT (',') FOR [new_affiche]

 

10.创建索引 

--创建聚集索引：
create index idx_col on tb(col)

--创建非聚集索引：
create nonclustered index idx_col on tb(col)

 

11.重建索引
DBCC INDEXDEFRAG (DB_MRP,PERSON,idx_person)  
注:
参数1为数据库名
参数2为表名
参数3为要重建的原索引名

--指定表名
DBCC DBREINDEX (tblWFProcessInstance, '',  70) 

--对全库
exec sp_msforeachtable 'DBCC DBREINDEX(''?'')'  

 

 

oracle 建表时加check约束或unique约束

1.加check约束或unique约束
create table temp(
gender varchar2(10) constraint ck_agents_gender check((gender='m')or(gender='f')),
cellphone nvarchar2(20) constraint uu_agents_cellphone unique);

 2.加日期默认值

alter   table   tablename   add   col   date1   default   sysdate

alter   table   tablename   modify   col   date1   default   sysdate