if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__titleauth__au_id__0519C6AF]') and OBJECTPROPERTY(id, N'IsForeignKey') =  1 )
ALTER TABLE [dbo].[titleauthor] DROP CONSTRAINT FK__titleauth__au_id__0519C6AF
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[authors]') and OBJECTPROPERTY(id, N'IsUserTable') =  1 )
drop table [dbo].[authors]
GO

if exists (select * from dbo.systypes where name = N'id')
exec sp_droptype N'id'
GO

setuser
GO

EXEC sp_addtype N'id', N'varchar (11)', N'not null'
GO

setuser
GO

CREATE TABLE [dbo].[authors] (
	[au_id] [id] NOT NULL ,
	[au_lname] [varchar] ( 40 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[au_fname] [varchar] ( 20 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[phone] [char] ( 12 ) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[address] [varchar] ( 40 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[city] [varchar] ( 20 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[state] [char] ( 2 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[zip] [char] ( 5 ) COLLATE Cyrillic_General_CI_AS NULL ,
	[contract] [bit] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[authors] ADD 
	CONSTRAINT [DF__authors__phone__78B3EFCA] DEFAULT ('UNKNOWN') FOR [phone],
	CONSTRAINT [UPKCL_auidind] PRIMARY KEY  CLUSTERED 
	(
		[au_id]
	)  ON [PRIMARY] ,
	 CHECK ([au_id] like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'),
	 CHECK ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
GO