Özgür ACAR Özgür ACAR - 2 months ago 6
SQL Question

EmployeeDocuments table can not be created in the database

I'm trying to create database and related tables. Everything works except from 'EmployeeDocuments' table (database and 4 tables were created) . It gives an error like:


There is already an object named 'FK_Employees_Companies1' in the database.


My codes are as below:

private void UserEntryForm_Activated(object sender, EventArgs e)
{
CheckIfDBExist();
}
int i;

private void CheckIfDBExist()
{

SqlConnection tmpConn = new SqlConnection("server=(local)\\SQLEXPRESS;user=sa;pwd=123");

string sqlCreateDBQuery = string.Format("if not exists (select name from sys.databases where name = 'Devrimer_v2222')create database Devrimer_v2222");

SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn);

string constr = Tools.Baglanti.ConnectionString.ToString();

try
{
tmpConn.Open();
int sonuc = sqlCmd.ExecuteNonQuery();

if (sonuc != 0)
{
tmpConn.Close();

string query = "if not exists(select * from sys.tables where name = 'Companies' ";
query += ")";
query += "CREATE TABLE [dbo].[Companies]([Id][int] IDENTITY(1, 1) NOT NULL,";
query += "[Name][nvarchar](100) NULL,";
query += "[Adress][nvarchar](500) NULL,";
query += "[TelNo][char](20) NULL,";
query += "[Active][bit] NOT NULL CONSTRAINT[DF_Companies_Active] DEFAULT((1)),";
query += "CONSTRAINT[PK_Companies] PRIMARY KEY CLUSTERED([Id] ASC ) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,";
query += "IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,";
query += "ALLOW_PAGE_LOCKS = ON) ON[PRIMARY] ) ON[PRIMARY]";
query += " SET ANSI_PADDING OFF";


using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}

string docQuery = "if not exists(select * from sys.tables where name = 'Documents' ";
docQuery += ")";
docQuery += "CREATE TABLE [dbo].[Documents]([Id] [int] IDENTITY(1,1) NOT NULL,";
docQuery += "[Name] [nvarchar](50) NULL,";
docQuery += "[Description] [nvarchar](500) NULL,";
docQuery += "[ValidPeriod] [int] NULL,";
docQuery += "[PeriodType] [nchar](10) NULL,";
docQuery += "[Active] [bit] NOT NULL CONSTRAINT [DF_Documents_Active] DEFAULT ((1)), ";
docQuery += "CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, ";
docQuery += "STATISTICS_NORECOMPUTE = OFF, ";
docQuery += "IGNORE_DUP_KEY = OFF, ";
docQuery += "ALLOW_ROW_LOCKS = ON, ";
docQuery += " ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]";

using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(docQuery))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
string EmpQuery = "if not exists(select * from sys.tables where name = 'Employees' ";
EmpQuery += ")";
EmpQuery += "CREATE TABLE [dbo].[Employees]( [Id] [int] IDENTITY(1,1) NOT NULL,";
EmpQuery += "[Name] [nvarchar](50) NULL,";
EmpQuery += "[SurName] [nvarchar](50) NULL,";
EmpQuery += "[Title] [nvarchar](50) NULL,";
EmpQuery += "[Gender] [tinyint] NULL,";
EmpQuery += "[MaritalStatus] [tinyint] NULL,";
EmpQuery += "[Tckn] [char](11) NULL,";
EmpQuery += "[ReportTo] [int] NULL,";
EmpQuery += "[BirthDate] [date] NULL, ";
EmpQuery += "[TelNo] [char](20) NULL, ";
EmpQuery += "[Adress] [nvarchar](500) NULL, ";
EmpQuery += "[Email] [nvarchar](250) NULL, ";
EmpQuery += "[CompanyID] [int] NULL,";
EmpQuery += "[StartWorkingDate] [date] NULL, ";
EmpQuery += "[Active] [bit] NOT NULL CONSTRAINT [DF_Employees_Active] DEFAULT ((1)), ";
EmpQuery += " CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, ";
EmpQuery += "STATISTICS_NORECOMPUTE = OFF, ";
EmpQuery += "IGNORE_DUP_KEY = OFF, ";
EmpQuery += "ALLOW_ROW_LOCKS = ON, ";
EmpQuery += "ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ";
EmpQuery += ";";
EmpQuery += "SET ANSI_PADDING OFF ";
EmpQuery += "ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Companies1] FOREIGN KEY([CompanyID]) REFERENCES [dbo].[Companies] ([Id]) ";
EmpQuery += ";";
EmpQuery += "ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Companies1] ";

using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(EmpQuery))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}

string userQuery = "if not exists(select * from sys.tables where name = 'Users' ";
userQuery += ")";
userQuery += "CREATE TABLE [dbo].[Users]( [Id] [int] IDENTITY(1,1) NOT NULL, ";
userQuery += "[EmployeID] [int] NULL, ";
userQuery += "[UserName] [nvarchar](50) NULL, ";
userQuery += "[Password] [nchar](10) NULL, ";
userQuery += "[Active] [bit] NOT NULL CONSTRAINT [DF_Users_Aktif] DEFAULT ((1)), ";
userQuery += " CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, ";
userQuery += "STATISTICS_NORECOMPUTE = OFF, ";
userQuery += "IGNORE_DUP_KEY = OFF, ";
userQuery += "ALLOW_ROW_LOCKS = ON,";
userQuery += "ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ";

using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(userQuery))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}

string docEmpQuery = "if not exists(select * from sys.tables where name = 'EmployeeDocuments' ";
docEmpQuery += ")";
docEmpQuery += "CREATE TABLE [dbo].[EmployeeDocuments]( [EmployeeID] [int] NOT NULL, ";
docEmpQuery += "[DocumentID] [int] NOT NULL, ";
docEmpQuery += "[GivenDate] [date] NULL, ";
docEmpQuery += "[LastValidDate] [date] NULL, ";
docEmpQuery += "CONSTRAINT [PK_EmployeeDocuments] PRIMARY KEY CLUSTERED ( [EmployeeID] ASC, ";
docEmpQuery += "[DocumentID] ASC )WITH (PAD_INDEX = OFF, ";
docEmpQuery += "STATISTICS_NORECOMPUTE = OFF, ";
docEmpQuery += "IGNORE_DUP_KEY = OFF, ";
docEmpQuery += "ALLOW_ROW_LOCKS = ON,";
docEmpQuery += "ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]";
docEmpQuery += ";";
docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] WITH CHECK ADD CONSTRAINT [FK_EmployeeDocuments_Documents1] FOREIGN KEY([DocumentID])REFERENCES [dbo].[Documents] ([Id]) ";
docEmpQuery += ";";
docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] CHECK CONSTRAINT [FK_EmployeeDocuments_Documents1] ";
docEmpQuery += "; ";
docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] WITH CHECK ADD CONSTRAINT [FK_EmployeeDocuments_Employees1] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].Employees] ([Id]) ";
docEmpQuery += ";";
docEmpQuery += "ALTER TABLE [dbo].[EmployeeDocuments] CHECK CONSTRAINT [FK_EmployeeDocuments_Employees1] ";

using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(docEmpQuery))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}

catch (Exception hata)
{
MessageBox.Show(string.Format(sqlCmd.CommandText.ToString() + "\n\nHata: " + hata.Message.ToString()));
}
finally
{
tmpConn.Close();
}
}
}

Answer

It is because you check to see if the table exists before creating it, yet you always try and create the constraints regardless of whether or not they exist, you have something like:

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Employees')
    CREATE TABLE dbo.Employees (....)
ALTER TABLE dbo.Employees WITH CHECK ADD CONSTRAINT ....

With this syntax it is only the statement immediately following the IF that will be conditional, a simply example would be:

IF 1 = 0
PRINT '1';
PRINT '2';

Which will simply print "2". If you want to have multiple statements executed conditionally, you need to wrap them in a statement block (BEGIN/END)

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Employees')
BEGIN
    CREATE TABLE dbo.Employees (....)
    ALTER TABLE dbo.Employees WITH CHECK ADD CONSTRAINT ....
END

For what it is worth, you could use a verbatim string literal to make everything a bit more legible (and easier to edit). e.g.

string query = @"IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Employees')
                BEGIN
                    CREATE TABLE dbo.Employees (....)
                    ALTER TABLE dbo.Employees WITH CHECK ADD CONSTRAINT ....
                END;";