Database Design Problem with Hibernate Primary Key and Foreign Key
I am a newbie to Hibernate. It seems to me that Hibernate does not
allow you to declare both id and composite-id in .hbm.xml mapping.
That was why i have a triple primary key in my ENROLLED table as
CREATE TABLE ENROLLED ( SID INTEGER NOT NULL,
CID INTEGER NOT NULL,
ENROLL_SINCE DATETIME NOT NULL,
EID INTEGER NOT NULL auto_increment,
COURSE_ROLE_ID INTEGER DEFAULT 0,
IS_AVAILABLE CHAR(1) DEFAULT 'Y',
CREATION_DATE DATETIME ...Design Question: Primary Key
Just a quick question.
I just inherited a database a week or so ago, and I noticed something in one
of the main tables that caught my attention.
The USERS table has the primary key as User_ID Varchar(50)
I was always taught that the primary key shouldn't mean anything, and in
this case..the User_ID is what the user types in when they register.
What would you suggest?
>> I was always taught that the primary key shouldn't mean anything.. <<
I am not sure why and how you were taught, but have you ever questioned why?
( Please reply to newsgroups only )
No chance to ask why. Like I said....it was inherited....and no idea who
the developer was.
But...there must be the RIGHT way to do it.
Should a Primary Key be a varchar and be something the user can enter?
"Anith Sen" <email@example.com> wrote in message
> >> I was always taught that the primary key shouldn't mean anything.. <<
> I am not sure why and how you were taught, but have you ever questioned
> -- Anith
> ( Please reply to newsgroups only )
On Wed, 29 Oct 2003 02:14:21 +0000, Member wrote:
> Just a quick question.
> I just inherited a database a week or so ago, and I noticed something in
> one of the main tables that caught my attention.
> The USERS t...newbie question--1 table's columns to link to other tables' primary keys
I'm new to databases, so if my question doesn't make any sense, don't
hold it against me. I have three tables--one stores user information
(username, password, email, etc), another stores a list of songs--this
table uses a 2-column primary key. One of the primary key columns is
of DATETIME type and the other is a SMALLINT AUTO_INCREMENT type. My
third table stores information about each listen--by that I mean a new
record is made everytime a user listens to a song, and in that record,
is the user and the song, so naturally there would only be two columns
in this table, namely User ...Designing a database within a database... design question storing data...
I have a system that basically stores a database within a database (I'm
sure lots have you have done this before in some form or another).
At the end of the day, I'm storing the actual data generically in a
column of type nvarchar(4000), but I want to add support for unlimited
text. I want to do this in a smart fashion. Right now I am leaning
towards putting 2 nullable Value fields:
ValueLong ntext nullable
ValueShort nvarchar(4000) nullable
and dynamically storing the info in one or the other depending on the
size. ASP.NET does this exact very thing in it's Session State model;...I am trying to set up a database using the database connectivity toolkit and 8.5. I have tables setup and each one has an autoincrementing primary ID key.
I have very little experience with databases, but I have to set one up. I am setting up a relational BD, I have several tables set up, each has a primary key set up as a ID, autoincrement.
How can I not send this column data, and let it autoincrement like it should so that it is a unique entry. I keep on getting an error that says that the number of colums don't match. Is there any way to do this.
I want the database to keep track of the line count for this, and increment when each new entry arrives. Does this make any sense?
The insert data VI has a Colu...BUG #1055: no keys in inherited table with primary key when inserting into inheriting table
The following bug has been logged online:
Bug reference: 1055
Logged by: Agri
Email address: firstname.lastname@example.org
PostgreSQL version: 7.4
Operating system: PC-linux-gnu
Description: no keys in inherited table with primary key when
inserting into inheriting table
let me desribe a bug in the term of sql commands:
create table first (id int primary key );
create table second (f2 int) inherits (first);
create table third (ref_id int);
alter table third add constraint third_ref_first foreign key (ref_id)
insert int...Database design question
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate o...Database/Table Design Question
I created a database to support an application that tracks events on
different objects. The two main tables are tbl_Object and
tbl_EventLog. Each table has unique ID and on the tbl_EventLog there
is FK for a record in the tbl_Object. The events are inserted all the
time for the same or different objects from the tbl_Object. There are
about 600,000 objects in the tbl_Object and 1,500,000 (and growing)
events in tbl_EventLog.
The user often wants to know what the last event was for a specific
What is the best way of retrieving the last event?
Should I simply do a...Database Design Vendors and Items Question. Do I need a third table?
Hi, probably a simple question that I have for what I think is a simple
database. I have a Vendors table and an Items table. There is only
one items, but several Vendors may have that same item. I have to have
a third table, correct, to connect them?
I was thinking
OrderDetails (or something like that)
ItemSerial (I thought about this because each vendor might have the
same vendor, but different serial numbers, which I cannot put in the
Purchased (y/n field)
I can then select all items and the vendors or a vendor and all the
item...Design Question -> Copying a table in another database, the relinking it.
I have a situation where I have a split database. At the back end, I
- create a new table (I will call it newTable) with the exact fields,
and relationships as another table (let's call it oldTable, and I need
to copy everything EXCEPT the data).
- Then I need to delete oldTable, and rename newTable 'oldTable'
The problem is, that all of this happens at the back end, and I'm
having some problems making this work.
I've tried using CreateTableDef, and this creates the
definitions...but it never manages to actually create a table (that I
I've also tried the following SQL,
SELECT * INTO newTable FROM oldTable Where 0 = 1;
The 0=1 will never occur, thus I will have a new table with the proper
definitions, but no data...which is what I want. But this creates a
table at the front end :( I need a table at the back end which is
Anyone have any ideas?
Copy oldTable. Paste. When you paste you will be given three options. One
will allow you to copy the structure only.
Easier though to open the oldTable, press Ctrl+a and then press delete.
Voila! All the data is gone and you have a blank oldTable.
>I have a situation where I have a split database. At the back end, I
>- create a new table (I will call it newTable) with the exact fields,
>and relationships as another table (let's call it oldTable, and I need
>to copy everything EXCEPT the data).
&g...Unknown primary keys and foreign keys ina database
I would like to connect to a database and determine, on the fly, what
columns are the foreign and primary keys for each table. Is there
any way to do this using the LabView Database connectivity toolkit or a
Found it, thanks! For future reference (for anyone who might search for this topic)
Go to: Tools -> Options -> View and check "system" and "hidden"
objects to explore the structure of these tables. It's not
recommended to edit them directly for obvious reasons, but will at
least give an idea...Access Project
I have a created a SQL Database with a table called Tbl_Customer which
includes lots of Rows of customer information.
The primary Key is CustID which is an Identity (Auto Number)
I want to be able to create a new table called Tbl_Address which has
CustID as a foreign Key and I want to be able to add between 1 and 4
addresses (different types such as home, term time etc).
Eventually I want to be able to create a form so you can view the
customer details, and skip through the addresses in a sub form.
My problem is that as soon as I set the relationship as 1 to many, I
can no longer add any a...Primary Key on a table is not coming over when I import table in SSIS
SQL 2005 SP2. When I try to import a table from one server to another
using SSIS, the table imported on the new server does not have the
primary key which exists on the source table. Help please. Thanks.
*** Sent via Developersdex http://www.developersdex.com ***
There is a setting under the options to move it. Be sure all objects
are checked within the options that you need. By default I dont not
believe this is the case.
i would bet any indexes and any foreign keys besides primary keys did
not move over as well as this is also an option.
On Jul 21, 3:37=A0pm, Raziq Shekha...How do I find the primary key of a table by querying the system tables?
I've googled for this for a while but haven't found anything. Please could
someone tell me how I can find iF a table has a primary key and what is
the name of the constraint.
I have this so far (I'm on ASE 11.0 for Linux btw):
select i.name, o.name from sysindexes i, sysobjects o where i.id = o.id
and o.type = 'U';
I think all my primary key indexes start PK_, but I'm not sure. I figure
the primary key must be flagged somewhere, but I can't see anything
obvious on the system tables.
Try the "status" co...Two foreign keys pointing to the same primary key in other table
i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
my database structure is
table1 - students table
table 2 - subjects table
table 3 - subjects opted by students
in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.
ID -> primary key, FirstName, LastName, ........
ID->pri...General Design Question SQL Tables or Local Tables
A general design question:
Assuming I can figure out a way to link some local tables in an .MDB
file to my Access2000 .ADP database (any help on this is appreciated
as well), I'm wondering which of the following methods will yield
faster performance over a slow WAN network:
Method A: Creating tables on my SQL Server to store temporary records
that are linked to records in permanent SQL Server tables.
Method B: Linking tables in a seperate .MDB file on the client
workstation that are linked to records in permanent SQL Server tables.
My current .MDB database uses a dozen or so local table...Primary Key built from other Primary Keys?
Can a primary key of a table be built from primary keys of other tables?
For example, I have a table called 'Match' and the way I'd like to construct
it is that the primary key of this table is built from 'Home Team',
'Opposition', 'Location' (these are all entities with tables) and 'Date'
(this is an attribute of the 'Match' table). For your information, the 'Home
Team' table has 'Name' for it's primary key and 'Opposition' and 'Location'
both have 'Name' and 'Z...Relationships error, C# Visual Studio 2005 database bug?, "the columns in table XYZ do not match an existing primary key or UNIQUE constraint", copying columns
I replicated, using Frasier "Pro Visual C++/CLI" (2006), Chap 12, a
database that has a relationship between two tables, called Content
and Author, using a common column, called "AuthorID". I used the
Server Explorer insider Visual Studio 2005 to do this. It worked.
Now I tried the exact same thing from Visual Studio 2005 but this time
using C# not C++ as my language of choice. The interface is slightly
different, but I made sure everything was done as before. I used the
Server Explorer 'GUI' to do this, clicking and following the 'wizards'
as...How can i copy a table to a new table with primary key remain
How can i copy a table to a new table(structure and data) with primary
i have try to use Select * into table from old_table
However, i dont have any primary key which old_table have
could anyone help me
On 9 Mar 2006 18:29:53 -0800, email@example.com wrote:
From the database window choose the Tables list.
Select your table.
>How can i copy a table to a new table(structure and data) with primary
>i have try to use Select * into table from old_table
>However, i dont have any primary key which old_table have
I want to create a table with
member id(primary key for Students,faculty and staff [Tables])
and now i want to create issues[Tables] with foreign key as member id
but in references i could not able to pass on reference as or
condition for students, faculty and staff.
> I want to create a table with
> member id(primary key for Students,faculty and staff [Tables])
> and now i want to create issues[Tables] with foreign key as member id
> but in references i could not able to pass on reference as or
> condition for students, faculty ...Does a foreign key require 'primary key' in the pointed-to table?
I would like to use ignore_dup_key on one of my indexes, so that
inserts of rows which duplicate an existing primary key will be
ignored. But you can't declare a primary key constraint with
ignore_dup_key, so I tried making an index instead.
create table my_table (a int not null)
create unique clustered index idx
on my_table (a)
execute sp_primarykey 'my_table', a
New primary key added.
(return status = 0)
The sp_primarykey above is purely for documentation, so I've heard,
but I assume it c...A Question on Database design
We have requirement to develop an application in java which should
have data export/import option. This application will be deployed in
one global centre and in different regional centres. There is no
link/network connection between the global centre and region centre.
For this application, i have to design a database. The design should
support export/import functionality also. I have two approaches
towards the database design...
1. Create all tables with PKs(Primar key), as sequence generated ID
and FK constraints. If i take up this approach, While importing the
regional c...Database design question
This appears simple enough but I'm going round in circles with it.
I have a growing contacts database with the usual contact data , being used
to send mail merges via email and mail.
I need to categorise each contact record several ways
So contact 1 may belong to category 1 , contact 2 could belong to category 1
and category 2 , and so on.
The number of categories is intially fixed at 5 but will grow as the
database grows and the query needs become more complex.
I need to develope a structure that will allow easy creation of queries ,
easy updating of data entry forms as the categories gr...Table design question
constantly facing this design issue I decided to ask for some help.
Consider the tables below; there is a user with historical data and a
related table log:
CREATE TABLE user (
id INT NOT NULL ) <---------------------
CREATE TABLE user_history ( |
user_id INT NOT NULL, oo----------------|
valid_from DATETIME NOT NULL, |
name CHAR(100) NOT NULL, |
... ) |
CREATE TABLE log ( ...Database Design Questions
I'm really stuck on how to design this application, so I thought I'd see if
anyone had any general ideas on how to proceed. I'd say I'm an intermediate
level Access developer.
I volunteered to help my kid's school (a small non-profit) with a tremendous
need they have for a complete student administration database. I've
developed fairly complex databases before but this one is very unique in how
it must be designed.
The challenge is this. A record must be created that contains information
about the student and both parents (tracking begins with an i...