|
You must be careful how you create your tables. Make sure you have the right entities identified and then normalise them and create your tables accordingly.In some cases, the foreign key becomes part of the composite key(i.e. a multi-column primary key)
Since I am not familiar with what you have in mind, I will use a different example. Consider a database for students, courses, and registration
Student(StudentId, FirstName,LastName, DOB, SIN....)
Course (CourseID, CourseName)
Semester(SemesterId, StartDate, EndDate)
CourseReg(StudentId,CourseID,SemesterID)
The primary keys are bold and underlined.The primary key for CourseReg is a composite key made up of foreign keys. Foreign keys are not always used as part of the composite key; in general use the fewest possible columns in a composite key while maintaining normalisation(3NF is usually good enough)
|
|
|
|
|
Hello . Thanks for your help .I ask this question because i had a problem in my own design
I have 5 entities that are related to each other and with foreign keys that i am sure are needed < there is a Circle in my design and this circle causes anomalies in the database and for this reason database prevent the DML actions in some of my tables .
I am going to ask a total question from you
: What should I do to solve the problem with the circles that appear because the foreign key constraints
?
thanks
|
|
|
|
|
I am not sure what you mean by "circles" but I suppose you mean some sort of cyclic constraints.
Before you create tables, you need to list the entities first. Entities are things tangible or intangible for which you want to capture.Also, determine what kind of information each entity should contain. At this point you're not thinking of tables. Once you have determine the entities,
then think of them as tables, and then normalise them to 3NF.
Since I am not an engineer, I can't figure out what the entities were to start with. So, go back to the point before you created those tables, what were the entities or objects whose information you wanted to capture?
|
|
|
|
|
May be to help be better, explain what these tables are for
Properties(PropertyID (PK) , Name , HardwareTypeID)
Values( ValueID (PK) , ValueTitle , PropertyID (FK))
Hardware-Value( PVID (PK identity), ValueID (FK), HardwareID(FK))
|
|
|
|
|
yousefshokati wrote: and with foreign keys that i am sure are needed
Based on what you posted I am sure that some are are not needed.
I seriously doubt that there is any reason for Properites to have HardwareTypeID. That is just an enumeration which can stand on its own. You tie the enumeration to something else via Values and that is sufficient.
|
|
|
|
|
I'm using VS2008 with C# and SQL CF v3.5 to create an inventory application for a mobile computer. I have never worked with SQL Compact before, but I'm pretty sure I've got it right from the database perspective. I've added my DataSource, TableAdapters, queries to fill my ComboBoxes, etc. When I deploy to my WM 6.5 device, I've made sure that the SQL Compact CAB files have been installed, and that the database exists on the device. When I use any of the ComboBoxes in the form, they are all blank and none of them are populated. In VS 2008 when I 'Preview Data' it's all there. Not sure where to go from here to debug?
|
|
|
|
|
Hello all,
I dont know it might be an easy thing but i didnt work it around.
I have two tables and want to join them.
Normally my join statement is as follows:
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = co.CID
but this time the columns in the columns in the tables that has relation is similar but with one difference.
C.ID = '123'
co.CID = 'c123'
so how can i join this;
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = 'c' + co.CID
something like that is it possible? or i need to accomplish what i am trying in two different statements?
Thank you very much for your help
|
|
|
|
|
Your second join works on some systems. Did you try it on your system?
|
|
|
|
|
Yes I have tried but it is not accepting it.
When i try it I am getting thois error:
Dynamic SQL Error
expression evaluation not supported
Strings cannot be added or subtracted in dialect 3
|
|
|
|
|
I have found the solution. The solution is
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = ('s' || co.CID)
|
|
|
|
|
It seems u are using PL/SQL not T-SQL.
Good day,Good job,Good life
|
|
|
|
|
try this
SELECT c.Name, co.Name
FROM City C
inner JOIN Country co ON c.ID = co.CID
where C.ID = '123'
and co.CID = 'c123'
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Your query will return 0 rows.
|
|
|
|
|
|
Have you really thought this one through? How can c.ID (and co.CID) equal 123 and c123 simultaneously? Have you come across some new quantum extension to T-SQL that allows uncertainty in equality tests?
|
|
|
|
|
Use substring
SELECT c.Name, co.Name
FROM City C
JOIN Country co ON c.ID = Substring(co.CID,2,3)
|
|
|
|
|
Try
'c' + c.ID = co.CID
and not
c.ID = 'c' + co.CID
If C.Id is of integer type then cast it
'c' + Cast(c.ID as varchar(20))= co.CID
Try this
Declare @City table (Id int,Name Varchar(20))
Insert Into @City
Select 123,'City1' Union All Select 1,'City2' Union ALl Select 124,'City3' Union ALl Select 5,'City4'
Declare @Country table (Id int,CID Varchar(10),Name Varchar(20))
Insert Into @Country
Select 1,'c123','Country 1' Union All
Select 1,'1','Country 1' Union All
Select 1,'c5','Country 1'
SELECT c.Name, co.Name
FROM @City C
JOIN @Country co ON 'c' + Cast(c.ID as varchar(20))= co.CID
Hope this helps
Niladri Biswas
|
|
|
|
|
I have an SQLite database and I need to load the database as in-memory database. Is it any way to do this?
I am using ADO.NET Data Provider library for SQLite to do all the DB Operations.
Please help.
|
|
|
|
|
|
I already read this documentation, but did't find any solution..
|
|
|
|
|
|
|
I don't know. If ADO.NET does not provide backup methods, and does not support SQL backup commands, then you will need to call native code that does perform backups for you. That would require some P/Invoke stuff, whether you would isolate that in a wrapper is up to you.
|
|
|
|
|
Thanks for the input. I have written my own code to implement it.
Please find the bellow steps.
1. Import the below Native methods
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_init(IntPtr pTo, string toName, IntPtr pFrom, string fromName);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_step(int b, int n);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_finish(int backupObject);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_sleep(int interval);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_remaining(int remaining);
[DllImport("System.Data.SQLite.dll")]
public static extern int sqlite3_backup_pagecount(int pageCount);
2. Define the below constants
public const int SQLITE_OK = 0;
public const int SQLITE_BUSY = 5;
public const int SQLITE_LOCKED = 6;
3. Above APIs required database as IntPtr. But we have only a connection class [System.Data.SQLite.SQLiteConnection]. So we need to find out database handle from the connection object. I don't know whether it is a right way or not, anyway implement an extension method as shown below
public static IntPtr GetConnectionHandle(this SQLiteConnection connection)
{
IntPtr returnPointer = IntPtr.Zero;
FieldInfo[] fields = typeof(SQLiteConnection).GetFields(
BindingFlags.NonPublic | BindingFlags.Instance |
BindingFlags.DeclaredOnly);
object sqLiteBase = null;
object value = null;
if (connection.State != System.Data.ConnectionState.Open &&
connection.State != System.Data.ConnectionState.Fetching &&
connection.State != System.Data.ConnectionState.Executing)
{
return returnPointer;
}
foreach (FieldInfo info in fields)
{
if (string.Compare(info.Name, "_sql", true) == 0)
{
sqLiteBase = info.GetValue(connection);
break;
}
}
if (sqLiteBase != null)
{
fields = sqLiteBase.GetType().GetFields(
BindingFlags.NonPublic | BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo info in fields)
{
if (string.Compare(info.Name, "_sql", true) == 0)
{
value = info.GetValue(sqLiteBase);
break;
}
}
}
if (value != null)
{
fields = value.GetType().GetFields(
BindingFlags.Default | BindingFlags.NonPublic |
BindingFlags.Instance | BindingFlags.FlattenHierarchy);
foreach (FieldInfo info in fields)
{
if (string.Compare(info.Name, "handle", true) == 0)
{
returnPointer = (IntPtr)info.GetValue(value);
break;
}
}
}
return returnPointer;
}
4. Now implement the Load method, which will load the schema and data to the memory-db as in the documentation http://www.sqlite.org/backup.html[^]
SQLiteConnection sqliteConnection = null;
public bool Load(string databaseFile)
{
sqliteConnection = new SQLiteConnection("Data Source=:memory:");
sqliteConnection.Open();
if (!File.Exists(databaseFile))
{
return false;
}
IntPtr toHandle = sqliteConnection.GetConnectionHandle();
SQLiteConnection fromConnection = new SQLiteConnection(
string.Format("Data Source={0};Read Only=True", databaseFile));
fromConnection.Open();
IntPtr fromHandle = fromConnection.GetConnectionHandle();
int retValue = sqlite3_backup_init(toHandle,
"main", fromHandle, "main");
if (retValue > 0)
{
sqlite3_backup_step(retValue, -1);
sqlite3_backup_finish(retValue);
}
fromConnection.Close();
return true;
}
5. Do any query operations in the sqliteConnection (memory) and finally save it to the disk as shown below.
public bool Save(string databaseFile)
{
bool isSuccess = false;
SQLiteConnection fileConnection = new SQLiteConnection(
string.Format("Data Source={0};Version=3;New=true", databaseFile));
try
{
fileConnection.Open();
IntPtr pFileTo = fileConnection.GetConnectionHandle();
IntPtr pFileFrom = sqliteConnection.GetConnectionHandle();
int pBackup = sqlite3_backup_init(pFileTo, "main",
pFileFrom, "main");
int rc = 0;
if (pBackup > 0)
{
do
{
rc = sqlite3_backup_step(pBackup, 10000);
if (SaveProgressEvent != null)
{
SaveProgressEvent(
sqlite3_backup_remaining(pBackup),
sqlite3_backup_pagecount(pBackup));
}
if (rc == SQLITE_OK ||
rc == SQLITE_BUSY ||
rc == SQLITE_LOCKED)
{
sqlite3_sleep(250);
}
}
while (rc == SQLITE_OK ||
rc == SQLITE_BUSY ||
rc == SQLITE_LOCKED);
sqlite3_backup_finish(pBackup);
}
isSuccess = true;
}
catch
{
}
finally
{
if (fileConnection.State != ConnectionState.Closed)
{
fileConnection.Close();
}
}
return isSuccess;
}
|
|
|
|
|
Prathapachandran.v wrote: and I need to load the database as in-memory database
Why?
And in terms of that is this going to be a read only database? If not then what happens when you write something to it?
And how are you going to load it when your application starts?
|
|
|
|