primary-key

To get primary and foreign keys right in ERD

I read my notes and I am now unsure about the keys. My logical diagram This table should be in line with the following physical ERD I have only one foreign key in the table Question. I would like to know should the question-id and question-tag in the table Question-tag-xref, AND question-id and answer in the table Answers be a...

Changing MySQL primary key when foreign key contraints exist

I have two already-existing tables which look (in part) roughly like this: CREATE TABLE parent ( old_pk CHAR(8) NOT NULL PRIMARY KEY ) ENGINE=InnoDB; CREATE TABLE child ( parent_key CHAR(8), FOREIGN KEY (parent_key) REFERENCES parent(old_pk) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB; I want to add a new ...

What is the best way to ensure that primary key and foreign key never intersect?

Say I have a table with a primary key a_id and foreign key b_id. It is important that a_id and b_id never intersect, i.e. it should never be the case that there exists an a_id = b_id. What would be the best way to implement this? Could I somehow do this on the database end (mySql), or should this be ensured programmatically? If I ens...

Core data: I want the pk in my fetched objects

I want to have the pk in my fetched objects, so i can use the unique pk number for a unique image filename. But i can't make it work, i just need a unique filename for my images. Does somebody has a solution for this? When i NSLog object ID i get this: NSManagedObjectID *ID = [someThing objectID]; NSLog(@"ID: %@",ID); Output: ID: 0x1...

how to use recently entered id in another table for primary key

declare @@newid int, @@newRequestId int INSERT INTO Projects (ProjectName) VALUES ('first') SELECT [@@newId] = SCOPE_IDENTITY() -- Insert statements for procedure here INSERT INTO Requests (projectId, Title, RequestedBy, ReviewedBy, AssignedTo, EstimatedHours, ActualHours, Status, DateCreated, DateDue, DateAssigned, DateQC, De...

SQLite dropping a column using temporary table, can I retain primary key?

I am wanting to drop a column from a table in my SQLite database, and it seems there is no support for dropping columns with ALTER TABLE. Currently, I am using CREATE TABLE AS to create a new temporary table (minus the column I don't want) from one of my existing tables. I planned on dropping the original table and renaming the temporary...

Using Rails, how can I set my primary key to not be an integer-typed column?

I'm using Rails migrations to manage a database schema, and I'm creating a simple table where I'd like to use a non-integer value as the primary key (in particular, a string). To abstract away from my problem, let's say there's a table employees where employees are identified by an alphanumeric string, e.g. "134SNW". I've tried creating...

Is it possible to constrain an auto-incrementing primary key with artificial limits?

Can an auto-incrementing primary key be constrained by artificial limits? For example if I only want integer primary keys to be from a specific range of integers, say between 100 and 999 inclusive, and auto-increment, is that possible? And if so, on which database server software. I'm mainly interested in MS SQL Server 2000 or greater bu...

Are there other strategies available for auto-incrementing a primary key besides the default x+1?

Are there other strategies available for auto-incrementing a primary key? So instead of the usual x+n where n is 1, could n be some other number such as 5? Or perhaps a random number but such that the next ID is still unique? I'm not sure what the uses would be, or even if it's a good idea, I'm just curious. ...

To show correctly PRIMARY KEY in Postgres

Which one of the following ways would you use in declaring Primary Keys by Postgres? #1 CREATE TABLE user( user_id PRIMARY KEY, ... ) #2 CREATE TABLE user( user_id NOT NULL, ... CONSTRAINT user_pk PRIMARY KEY(user_id); ) ...

Primary key defined by many attributes?

Can I define a primary key according to three attributes? I am using Visual Paradigm and Postgres. CREATE TABLE answers ( time SERIAL NOT NULL, "{Users}{userID}user_id" int4 NOT NULL, "{Users}{userID}question_id" int4 NOT NULL, reply varchar(255), PRIMARY KEY (time, "{Use...

how to make a composite primary key (java persistence annotation)

how to make it so that the table user_roles defines the two columns (userID, roleID) as a composite primary key. should be easy, just can't remember/find. in userdao @ManyToMany(fetch = FetchType.LAZY) @JoinTable(name = "user_roles") public List<RoleDAO> getRoles() { return roles; } @Id @GeneratedValue(strategy = ...

MS-Access - how to find the name of the composite primary key and associated fields

Hey all I have a bunch of Access databases, each of which has several tables. Each table has a composite primary key, with varying fields comprising that key. How can I, for a given table, get the name of the primary key and the names of the fields which are used in it? I.e., what's the SQL to do that? (I need to use raw SQL rather tha...

how to build one to many rows in tokyo cabinet?

i quote from the tokyo cabinet docs... As for database of hash table, each key must be unique within a database, so it is impossible to store two or more records with a key overlaps. or does tokyocabinet allow tuple based keys ? what would be the best way to set a one-to-many store ( like a crawler 1 kw<->many docids ) ~B ...

surrogate vs natural key: hard numbers on performance differences?

There's a healthy debate out there between surrogate and natural keys: SO Post 1 SO Post 2 My opinion, which seems to be in line with the majority (it's a slim majority), is that you should use surrogate keys unless a natural key is completely obvious and guaranteed not to change. Then you should enforce uniqueness on the natural key...

Oracle primary keys: NUMBER vs NUMBER(7,0)

Is there any benefit to specifying the precision on the PK? Is 7,0 sufficient, given that there will probably never be more than a few thousand records? Any dangers to not specifying the precision? ...

Primary Key Effect on Performance in SQLite

Hi, I have an sqlite database used to store information about backup jobs. Each run, it increases approximately 25mb as a result of adding around 32,000 entries to a particular table. This table is a "map table" used to link certain info to records in another table... and it has a primary key (autoincrement int) that I don't use. sqli...

one primary key column foreign key to 2 other table columns.How to resolve data entry issue.

I have a requirement according to which I have to create a central Login system.We have 2 things Corporate and Brand each represented by tables "Corporate" and "Brand". When a corporate gets registered,corporateID is given,When a user under that corporate gets registered there is a table corporateuser in which corporateID is a foreign k...

Getting the primary key value after a merge command?

Is there some way to get a value from the last inserted or updated row? I am using the merge command to do an insert or an update if the row exists. I know how to get the autogenerated key after an insert but can I get the primary key if I use the merge command? I'm using Java with JDBC and Oracle DB. ...

looking how to realise a primarykey in a xml schema with autoincrement function

At the beginning i wrote a c# class. Then i created a schema for the class with the “xsd.exe” tool. I posted this schema into the schema collection of my database and created a new xml column: CREATE XML SCHEMA COLLECTION AppointmentSC AS 'its impossible to insert a schema here in the forum' GO ALTER TABLE Adresse ADD xmlAppointment XM...