FAQ Database Discussion Community


Using Hibernate Get with Multi-Column Primary Key

java,hibernate,primary-key,composite-primary-key
Say I have a class that looks like this: public class MyClass { @Id @Column(name = "ID") private long Id; } I can use a hibernate session to do a get or load on the class like this: MyClass a = (MyClass)session.get(MyClass.class, new Long(100)); However, assume I have a class...

Cassandra - querying on clustering keys

cassandra,primary-key,cql,clustering-key
I am just getting start on Cassandra and I was trying to create tables with different partition and clustering keys to see how they can be queried differently. I created a table with primary key of the form - (a),b,c where a is the partition key and b,c are clustering...

Can a Field act as both Foreign and Primary Keys in the same table?

database,foreign-keys,relational-database,primary-key,flask-sqlalchemy
I am working with Flask-SQLAlchemy, but I guess this question cuts across different database implementation platforms. Suppose I have two table models structured as: User (with fields: user_id as PRIMARY KEY, username, email, password) and UserDetails (with fields: f_name, l_name, dob, gender, address, contact, bio and user_id as FOREIGN KEY)....

SQL Return list of tables containing foreign keys related to a primary key

mysql,sql,database,foreign-keys,primary-key
I am trying to query the mySQL database to return a list of tables containing a foreign key associated with a primary key. For example, I have a table containing "reference_number" and other tables containing "reference_number" as a foreign key. How would I query the database to return the list...

Can the field type of a foreign key be different to the referenced primary key?

mysql,database,foreign-keys,primary-key
I get a E-R model which is not designed by me. But I have no way to contact the author. There are something confusing me in this model. In Table A, the Primary Key is an integer field. Table B has a Foreign Key referenced to the PK of A....

SQL Server : there are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key 'FK'

sql-server,primary-key
I've been trying to solve this error for several minutes but I don't know what am I missing from table definition. The code for the tables are below: Table Autocare: CREATE TABLE [dbo].[Autocare] ( [IDAutocar] NUMERIC (18, 0) NOT NULL, [IDTipAutocar] NUMERIC (18, 0) NOT NULL, PRIMARY KEY CLUSTERED ([IDAutocar]...

Django Rest Framework: Deserializing and get the primary key from validated_data

json,django,primary-key,django-rest-framework,nested-lists
I defined a nested model Product as follow. Each Product can belong to a lot of Productlist. class Product(models.Model): product_id = models.AutoField(primary_key=True) product_name = models.CharField(max_length=50) class Productlist(models.Model): productlist_id = models.AutoField(primary_key=True) productlist_name = models.CharField(max_length=50) product = models.ManyToManyField(Product, related_name='productlists') The corresponding serializers are: class ProductlistSerializer(serializers.ModelSerializer): class Meta: model =...

SQL Unique Key Violation

sql-server,primary-key
I am getting Unique Key violation and I am not sure why, here is my scenario. I have a table with the following fields: ID, LocationID, Name, Description, Dewey The ID is obviously the primary key, LocationID is a foreign key linked to the Location table and the other fields...

MySQL syntax error when creating a table with 2 foreign keys as primary key

mysql,foreign-keys,primary-key
I keep getting a 1064 error when I run the below query but I don't understand what's wrong with it: CREATE TABLE hashmkb_mangatracker.group_release ( group_id int NOT NULL, release_id int NOT NULL, PRIMARY KEY (group_id, release_id), UNIQUE INDEX (release_id, group_id), FOREIGN KEY (group_id) REFERENCES group(id), FOREIGN KEY (release_id) REFERENCES release(id)...

Can a unique key ( not a primary key) be a foreign key to other table?

oracle,foreign-keys,primary-key,unique-key
I have two table students and studentsprofilepic 'username' from students is unique key of the table it is referenced as foreign key for the 'studentsprofilepic' table the DDL for the tables are create table students( id number, username varchar2(30), password varchar2(30), firstname varchar2(30), lastname varchar2(40), email varchar2(300), dob varchar2(20), alt_email...

mysqli_result — Need a string value from result

php,mysql,database,mysqli,primary-key
I have a query that returns the primary key of a table (this table is stored in an array of tables from a database) written in PHP: $link: connection to a database $tables: array of database tables $x: loop variable $primaryKey = mysqli_query($link, "SHOW INDEX FROM ".$tables[$x]); mysqli_query returns a...

Update even though using constraints

sql-server,sql-update,foreign-keys,primary-key,sql-server-2014
I'm trying to update a value (small spelling error) in the SQL Server 2014 database. I do like this: update t_Table set funID = 'References' where funID = 'Referencies' When doing this I get the error The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_t_Table_Language_t_Table". The conflict occurred in...

MySql - Is primary key unique by default?

mysql,primary-key,unique-key
If I define a column as a primary key in MySql, is it also unique key by default or do I need to also define it as unique key (in case I want it to be unique)? I saw this question What is the difference b/w Primary Key and Unique...

Joins etc. with GUID as Primary Key on SQL Server

sql-server,database-design,primary-key,guid
Looking at this post: [What are the best practices for using a GUID as a primary key, specifically regarding performance? I understand that if I want to use a GUID as the primary key then this should be non-clustered pk and then also create a clustered int unique index (autoinc)....

Why are my primary and secondary DocumentDB keys not showing in the Azure portal?

azure,primary-key,azure-documentdb
I just created a new DocumentDB instance. When I go to the "Keys" area on the new Azure portal, I don't see a primary key or secondary key. My URI is listed. I tried use the regenerate feature hoping that would create the keys, but no luck. Anyone else have...

C# Datagridview don't show primary key

c#,sql,gridview,primary-key
Currently I'm setting up a gridview, this is my code: //tabel conn.Open(); sda = new SqlDataAdapter(@"SELECT Totaal,Nr,Datum,Beginstand,Eindstand,van,Naar,Bezoekadres,Geredenroute,Karakterrit,Toelichting,KilometersZakelijk,KilometersPrive from ars.Kmstand", conn); dt = new DataTable(); sda.Fill(dt); metroGrid1.DataSource = dt; I don't want to show the primary key (Totaal) but when I don't use it in the query the table won't update....

MySQL Modeling: Relationship normalization & ring issue

mysql,relational-database,primary-key,relationship
I'm building this MySQL model to organize a movie collection. It will manage both movies and series/episodes. Each movie/episode may have one or more audio stream and codecs for audio/video may vary. (don't mind subtitles for now) I'm not an expert, so I'm seeking advice: Ring relationships. Someone told me...

Sql Lite Primary key for Application

android,sqlite,primary-key
Hi can anyone one suggest me a primary key(type:long) implementation which is for each installed android application. This will help me much. Checked below link. But it will return type string. Identifying-app-installations Thanks in advance....

What is the advantage of using composite primary key instead of single primary key in this context?

mysql,cakephp,database-design,primary-key,composite-primary-key
Cakephp v3 is now able to support composite primary key. http://book.cakephp.org/3.0/en/quickstart.html One example given at the quick start guide; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, created DATETIME, modified DATETIME ); CREATE TABLE bookmarks ( id INT AUTO_INCREMENT PRIMARY KEY,...

SQL Ignore duplicate primary keys

sql,sql-server,tsql,primary-key
Imagine you have a string of results from a SELECT statement: ID (pk) Name Address 1 a b 1 c d 1 e f 2 a b 3 a d 2 a d Is it possible to alter the SQL statement to get one record ONLY for the record with...

Is it possible to filter by 2nd cluster key without specifying 1st?

cassandra,primary-key
I am just starting to get a hand of cassandra. And I am curious if I can do the following: session.execute("CREATE TABLE IF NOT EXISTS mykeyspace.newtable (user_id int, item_id int, tstamp timestamp, PRIMARY KEY(user_id, item_id, tstamp))") My Understanding: I know that my user_id is a partition key, and I can...

Is there a performance penalty for retrieving the primary key as part of a “WHERE PrimaryKey =” SQL Statement

mysql,sql,database,performance,primary-key
Using the premise of a session table with 3 columns: id, skey and sdata, and a SELECT query on that table. I have 3 options for SELECT [...] WHERE id = 1: "SELECT * FROM sessions WHERE id = 1" "SELECT id, skey, sdata FROM sessions WHERE id = 1"...

C# Auto Increment Primary Key with String?

c#,sql,combobox,primary-key,auto-increment
I am newbie to C# and programming... When form loading primary key has to load automatically to comboBox and it has to automatically generate +1 from last value.. for example if COUNT value is 0 then it has to add like this EMP01 Here's my code: class PrimaryKeyIncrement { public...

Can foreign key references contain NULL values in PostgreSQL?

postgresql,reference,foreign-keys,primary-key
As an example create table indexing_table ( id SERIAL PRIMARY KEY, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), ); Is there a difference between the following tables? Table 1: create table referencing_table ( indexing_table_id INTEGER references indexing_table ); Table 2: create table referencing_table ( indexing_table_id INTEGER references indexing_table NOT NULL...

Wheres my Mistake?

java,database,prepared-statement,primary-key,sqlexception
I want to get the actual created Primary Key. I need it instantly for another Method but it returns an error. But it returns a SQLE. Ive no idea wheres my Mistake. I hope i gave you enaugh information. (The System.out.println(id) is just for me to check if it returns...

How to implement primary key / foreign key between two parse tables?

ios,parse.com,primary-key,foreign-key-relationship
Is there a way to make primary key and foreign key relationship with Parse tables? If yes, how? I've two tables: User Table: uId (primary key/string) | uName (string) | uAge (number) | uGender (string) | uLocation (string) Employment Table: eId (unique/string) | eType (number) | eSalary (number) | uId...

Get the key of an unknown entity [duplicate]

c#,entity-framework,primary-key,state,dbcontext
This question already has an answer here: Get the primary key value of an arbitrary entity in code first 1 answer Hello I would like to get the primary key value. public static void Look(LEBAEntities db, object obj) { // if getPrimaryKey(obj) == 0 db.Entry(obj).State = EntityState.Added; // else...

How to create concatened primary key?

mysql,primary-key
Hello I'm new to MySQL but I would like to create a unique ID based on two primary keys created in two other tables. Here is just the part that interests us: CREATE TABLE patient( id_patient int NOT NULL AUTO_INCREMENT, ..., PRIMARY KEY (id_patient)) CREATE TABLE surgeon( id_surgeonint NOT NULL...

Calling SELECT * on a primary key versus an attribute in SQL

mysql,sql,select,primary-key
I am currently studying for a final and have a question about one of the practice problems. The problem states: Consider the following two SQL queries over the relation R(A,B) where attribute A is the primary key. Query 1: SELECT * FROM R WHERE B > 50 OR B <=...

EntityType 'Attribute' has no key defined even though [key] is defined

c#,asp.net-mvc-4,entity-framework-5,primary-key
I am using Entity Framework 5 to access data from SQL Server 2008 R2 in an MVC 4 Application. This is my T_Attribute class: public partial class T_Attribute { public T_Attribute() { this.T_Characteristic = new HashSet<T_Characteristic>(); this.T_Category = new HashSet<T_Category>(); } [Key] public int AttributeId { get; set; } public...

SQL Error when creating a table

mysql,sql,database,primary-key
I have a problem when I run this SQL query: CREATE TABLE `softwaredb`.`profile` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `user_id` INT(11) NOT NULL , `gender` VARCHAR(255) NOT NULL , `height` INT(4) NOT NULL , `weight` INT(4) NOT NULL , `bodytype` INT(1) NOT NULL ) The error I keep running...

1 auto_increment with 2 primary keys

mysql,primary-key,innodb,auto-increment,myisam
My intention is to create a table with 2 primary keys with one of those autoincrementing and the other specified when inserting and when I create a new field for this table it must start the recount if the not incremented primary key changes. This is what I had: I...

Query in Oracle to get columns in a table with primary key info

oracle,primary-key
What is a query to get table description with columns with columns part of primary key tagged? For instance, it would return something like this: Table Column is_part_of_pk ----- ------ ------------- ADDRESS ID 1 ADDRESS ADDR_LINE_1 0 ADDRESS ADDR_CITY 0 STUDENT FIRST_NAME 1 STUDENT LAST_NAME 1 STUDENT CLASS_NAME 0 All...

Can prime attributes depend on each other?

database,primary-key,normalization,composite-key
I have a relation R{id, building, floor, sequence, capacity} where: (building,floor,sequence) -> id id -> (building,floor,sequence) (building,floor,sequence) -> capacity id -> capacity It seems that this doesn't violate any normal form. However, is it really ok for id to depend on (building,floor,sequence) and vice versa even though id is the...

Design a table structure

mysql,database,primary-key
I am new to programming and very new to Data Base. My problem is that I need to create a table faculty id(pk auto increment) fact_name department subjects Now my issue is that a faculty can have more then 2 or more departments or subjects, for me it is giving...

Should this two tables be related? [closed]

php,mysql,foreign-keys,primary-key,innodb
I have an account_table and a news_table. A news can be created by an admin account. The account_table has 4 columns which are: 1. account_id 2. username 3. password 4. user_type The news_table has 5 columns which are: 1. news_id 2. title 3. message 4. created_by 5. created_on If I...

MySQL Insert Statement foreign key auto add pk

php,mysql,foreign-keys,primary-key,sql-insert
I have a fairly complicated database set up with lots of foreign key relationships and want to know if there is an easy way to insert an entry into one of these tables. This is the table that holds some of the foreign keys. CREATE TABLE package ( id INT...

Is it necessary to use an integer ID for the Country table when the name is already univocal?

mysql,database,primary-key
Since the country name is univocal, can be a good choice to use it as a primary key? CREATE TABLE IF NOT EXISTS CookingDB.Country ( CountryName VARCHAR(50) NOT NULL, PRIMARY KEY (CountryName) ) The examples that I found online use an integer ID as the primary key. Is it necessary?...

Select 3 Distinct columns return 5 columns

sql,sql-server,primary-key
SELECT DoctorId, Plan, StartDate, EndDate,Status From DoctorContracts In DoctorContracts Table I need Distinct doctorid, plan and StartDate but I need to get Status and Enddate However There is a Group column which is either CA or NH. I need the minimum StartDate between CA or NH. Example DOCTORID. PLAN. STARTDATE....

How to change primary key type in postgreSQL to bigserial using Rails/ActiveRecord

ruby-on-rails,postgresql,activerecord,primary-key
How can I change the type of the primary key column of an existing table from serial (int) to bigserial (bigint) in postgres? The database is used by a Rails App. Do I need to make any changes to the app?...

Adding primary key to some exising tables in a database [SOLVED]

sql,sql-server,database,for-loop,primary-key
I have a database with over 600 tables. Some of the tables have primary keys and some do not. How can I dynamically: 1. loop through all tables 2. select ones that do not have primary keys 3. add an auto incremented field 4. make this field the primary key...

SQL copying record with out specifying column list; ignoring Identity

sql,sql-server,copy,primary-key,identity
I'm trying to copy a record from TableA back to TableA, but using a new Identity. I don't want to specify column list as I have over 100 columns, and there may be more in the future. Id like a chunk of code that can run when/if things change. After...

MYSQL Balance Sheet Design

mysql,foreign-keys,primary-key,entity-relationship,foreign-key-relationship
I'm working on creating an ERD Diagram to map out a MYSQL database. One of my entities in the ERD Diagram is Balance Sheet. How I have it setup right now is: Company PK Company ID Company Name Balance Sheet PK Balance Sheet ID FK Company ID Year Month I...

The differences between int and uuid in mysql

mysql,performance,primary-key
If I set the primary id key in int type (auto increment ) or set it in UUID, what is the difference between these two in the database performance (select, insert ...) and why?

Create A View, Select a Column As Primary Key

sql,entity,primary-key,sql-view
I have a long code line to create a view. For Entity framework to work with this view I need a primary key. How can I select the column as a primary key? I've tried this; dstchannel,billsec,disposition,accountcode, uniqueid as PRIMARY KEY,cnam, callRateImport.Description, callRateImport.Code, callRateImport.Day FROM callLogImport LEFT JOIN callRateImport ON...

Adding a primary key to a lookup table

sql-server,entity-framework-5,primary-key
We are using GUIDs for all of our SQL tables at work, and I was asked to create a lookup table to between 2 existing tables because of a many to many relationship between the tables. Let's say the original tables are Account, and AccountOwner. Accounts can have multiple owners,...

Integrity Error in Django 1.7

python,django,orm,primary-key
The following query throws an Integrity error when the about column is changed after first save. Primary key of page table is foreign key to pagedes table. page_id1 = models.Page.objects.get(page= selected_page_by_user) try: (save_des, check_des)= models.Pagedes.objects.update_or_create(page = page_id1, about = about, description = description) except: print 'page data_desc save error' ...

Insert row into table with only a primary key field

sql,postgresql,primary-key,auto-increment,sql-insert
Googling around told me to try this: insert into Plan default values; but I got: null value in column "id" violates not-null constraint or this: insert into Plan (id) values (null); but I got the same thing. The examples were for SQL: is there something different for PostgreSQL? Table structure:...

Should a primary key which is a hash be stored as BINARY or CHAR (in hex)?

sql,sql-server,primary-key
Records are identified by 32 byte SHA-2 value. Using BINARY is more compact (8 bits per byte vs 4 bits per byte) but that is offset by the increased difficulty in using binary value in adhoc queries (troubleshooting, etc). I am leaning towards storing as hex strings in 64 byte...

Doctrine - Missing value for primary key

sql,symfony2,doctrine2,primary-key
I have this entity: /** * Class ParserURLs * @package AppBundle\Entity * @ORM\Entity(repositoryClass="AppBundle\Entity\ParserURLsRepository") */ class ParserURLs { /** * @ORM\Id * @ORM\Column(type="string", length=50) */ protected $external_id; /** * @ORM\Id * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Parser") */ protected $parser; ... } Any I try to get results filtering by $parser, like this: $query = $this->doctrine->getManager()->createQuery(...

Delete single column from primary key in mysql

mysql,duplicates,primary-key,multicolumn
I have a table with a primary key that consists of 5 columns. I found out that one of these columns should not be in the primary key because it is creating duplicates. I'm trying and failing at removing this column (both from the primary key and just entirely from...