Menu
  • HOME
  • TAGS

How to create concatened primary key?

mysql,primary-key

You can create a computed column, and if you really want its values on disk you can persist the computed column CREATE TABLE case( id_patient int NOT NULL, id_surgeon int NOT NULL, id_case varchar(30) AS CONCAT(id_patient, '_', id_surgeon), CONSTRAINT FK_case_patient FOREIGN KEY (id_patient) REFERENCES patient(id_patient), CONSTRAINT FK_case_surgeon FOREIGN KEY (id_surgeon)...

MYSQL Balance Sheet Design

mysql,foreign-keys,primary-key,entity-relationship,foreign-key-relationship

I think I figured it out. The role version should work just fine because I will specify the balance sheet based on a month or year so the records for BalanceSheet_Role will display all roles made within that month or year

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

cassandra,primary-key

The answer to this is to build two tables. One with each primary key scheme you want. Duplicating data in tables with different schemas is relatively cheap since writes are so fast in Cassandra.

Spring mongo : How to customize _id without VO?

java,spring,mongodb,spring-mvc,primary-key

Simple solution but I think it is Orthodox way. Put an element into the ReturnMap. e.g. map.put("_id", map.get("service_id")); Now, I can prevent duplication of values. If there is better way, I will choose that one as an answer :D Thanks ...

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

azure,primary-key,azure-documentdb

The account can take several minutes to provision, during which time the keys will not be available (since the account has finished creating). If, in the portal, you choose to Browse | DocumentDB accounts, you will see the status (creating, updating, online). Once your account is in an online state,...

Delete the foreign key from MySQL

mysql,foreign-keys,key,primary-key

Assuming That you have created foreign key using constraint. ALTER TABLE users DROP FOREIGN KEY fk_users; //Your actual constraint name Hope This Helps....

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

oracle,foreign-keys,primary-key,unique-key

YES, The foreign key column establishes a direct relationship with a primary key or unique key column (referenced key) usually in another table: CREATE TABLE BOOK( BNAME VARCHAR2(10)NOT NULL UNIQUE, BTYPE VARCHAR2(10)); CREATE TABLE BOOKS_AUTH( A_ID INT NOT NULL, BNAME_REF VARCHAR2(10) NOT NULL, FOREIGN KEY (BNAME_REF) REFERENCES BOOK (BNAME)); SQLFIDDLE...

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

mysql,sql,select,primary-key

The correct answer is the second, because A is a primary key and a primary key cannot be null, so A > 50 OR A <= 50 will always be true, while the following: B > 50 OR B <= 50 might be NULL if B is NULL NULL >...

Cassandra - querying on clustering keys

cassandra,primary-key,cql,clustering-key

It's not that clustering keys are not treated the same, it's that you can't skip them. This is because Cassandra uses the clustering keys to determine on-disk sort order within a partition. To add to your example, assume PRIMARY KEY ((a),b,c,d). You could run your query (with ALLOW FILTERING) by...

Get all primary keys in a MySQL table using Java

java,mysql,primary-key

There is no method like getPrimaryKeyValues(). You need to iterate through the ResultSet to create your own list of values. Something like: String sql = "Select Name from yourTableNameHere"; Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery( sql ); List<String> names = new ArrayList<String>(); while (rs.next()) { names.add( rs.getString(1) );...

The differences between int and uuid in mysql

mysql,performance,primary-key

UUID returns a universal unique identifier (hopefuly also unique if imported to another DB as well) To quote from mysql doc (emphasis mine): A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even...

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

In a nutshell: clustering. InnoDB will automatically cluster (physically group together) bookmarks_tags rows with the same bookmark_id, making certain queries1 extremely fast because DBMS doesn't have to "jump" all over the table to gather all the related rows. There is no need for a surrogate key (auto-increment int) in this...

Can foreign key references contain NULL values in PostgreSQL?

postgresql,reference,foreign-keys,primary-key

For table 1, this INSERT statement will succeed. If you run it 100 times, it will succeed 100 times. insert into referencing_table values (null); The same INSERT statement will fail on table 2. ERROR: null value in column "indexing_table_id" violates not-null constraint DETAIL: Failing row contains (null). ...

Create A View, Select a Column As Primary Key

sql,entity,primary-key,sql-view

You cannot create primary keys on the view itself. To get around the no primary key issue, you can create a unique column that the entity framework will pick up and use. ISNULL(CAST((row_number() OVER (ORDER BY <columnName>)) AS int), 0) AS ID So: SELECT ISNULL(CAST((row_number() OVER (ORDER BY uniqueid)) AS...

The right data model for a JavaScript object with two primary keys

javascript,primary-key

Use child objects: locales = { 'A string': { context1: 'My localized string', context2: 'My localized string in another context' }, 'Another string': 'Another localized string' }; Another option would be to have different locales, per context: context1Locale = { ... }; context2Locale = { ... }; locale = {...

insert inserted id to another table

sql,postgresql,triggers,primary-key,entity-relationship

Avoid rules, as they'll come back to bite you. Use an after trigger on table a that runs for each row. It should look something like this (untested): create function a_ins() returns trigger as $$ begin insert into b (a_id) values (new.id); return null; end; $$ language plpgsql; create trigger...

ForeignKeyConstraint FK_ModelTime_Model requires the child key values (100) to exist in the parent table

c#,unit-testing,foreign-keys,relational-database,primary-key

Seems that you have enforced constraints on your dataset set to true. try: AvailabilityDS ds = new AvailabilityDS(); ds.EnforceConstraints = false; check here for some more info....

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

mysql,foreign-keys,primary-key

GROUP is a reserved keyword in MySql. The same goes for RELEASE. You will have to add backticks around those like this: 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)...

Gson. Deserialize to java object list of new elements with null id

java,json,gson,deserialization,primary-key

This method returns a primitive long. You cannot return a null from this method. public long getId() { return id; } Modify the return type to Long. The instance variable that this method is returning can be null, but the method itself cannot. public Long getId() { return id; }...

Get the key of an unknown entity [duplicate]

c#,entity-framework,primary-key,state,dbcontext

Here is several solutions: 1- Your entities inherit from a Base Class which base class has Id property and you make Look method generic: public abstract class BaseEntity { protected BaseEntity() { } public virtual int Id { get; set; } } public static void Look<TEntity>(LEBAEntities db, TEntity entity) where...

Generate unique value in MS SQL

sql,sql-server,sql-server-2008,random,primary-key

DECLARE PkStudentid as INT CREATE TABLE Student ( Studentid int IDENTITY (1, 1) NOT NULL PRIMARY KEY, Firstname nvarchar (200) NULL, Lastname nvarchar (200), Email nvarchar (100) NULL ) insert into Student (Firstname,Lastname,Email) Values('Vivek', 'Johari', ‘[email protected]'); SET @PkStudentid = SCOPE_IDENTITY(); print @PkStudentid This will insert Studentid as 1 and next...

What is Hash and Range Primary Key?

database,hash,nosql,primary-key,amazon-dynamodb

"Hash and Range Primary Key" means that a single row in DynamoDB has a unique primary key made up of both the hash and the range key. For example with a hash key of X and range key of Y, your primary key is effectively XY. You can also have...

how to compare non primary key in google app engine using java

java,eclipse,google-app-engine,primary-key,jdo

In UserAddress.java class userId is long when execute the query passed variable type is Stringso data is not fetching. AfterType cast the userId String to Long problem solved. Ex: Long.valueOf(userId). Query query = pmf.newQuery(UserAddress.class); query.setFilter("userId == userIdParam"); query.declareParameters("Long userIdParam"); returnList = (List<UserAddress>) query.execute(Long.valueOf(userId)); System.out.println(returnList.size()); ...

Symfony2 - Doctrine2 - MySql: Composite FK and PK

mysql,symfony2,doctrine2,foreign-keys,primary-key

Try this to change your Review entity like this Review: ... manyToMany: topics: targetEntity: Topic joinTable: name: reviews_topics joinColumns: review_id: referencedColumnName: idReview inverseJoinColumns: topic_id: referencedColumnName: idTopic inversedBy: reviews cascade: ["persist", "remove"] ...

Doctrine - Missing value for primary key

sql,symfony2,doctrine2,primary-key

Looks like you are missing name property in your annotations. Your definition should look like this: /** * @var string * * @ORM\Column(name="id", type="string", length=9) * @ORM\Id */ private $id; Where name="id" is your name of the field from you DB If your ID has autoincrement you have to add...

Get field value of inserted row

mysql,sql,function,primary-key,return-value

As pointed in the comments, from MySQL documentation: mysql> SELECT LAST_INSERT_ID(); -> 195 This LAST_INSERT_ID() function is not subject to a race condition like SELECT MAX(id) might be, because it's maintained within MySQL specific to your connection. It returns the ID generated for your last insert, not anybody's last insert....

mySQL column that is auto incremented every for every duplicate?

mysql,primary-key,auto-increment

You could use INSERT ... ON DUPLICATE KEY UPDATE, which performs an update instead of insert if the name you are attempting to insert already exists : INSERT INTO tablename (name,count) VALUES ('Shoes',1) ON DUPLICATE KEY UPDATE count=count+1; ...

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

c#,asp.net-mvc-4,entity-framework-5,primary-key

Deleting the Database and going over to Code-First solved the problem for me. Thanks for your help.

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

oracle,primary-key

Try this select col.table_name , col.column_name, case when exists(select 'x' from USER_CONSTRAINTS l join USER_CONS_COLUMNS ll on LL.CONSTRAINT_NAME = L.CONSTRAINT_NAME where l.table_name = col.table_name and l.constraint_type = 'P' and ll.column_name = col.column_name) then 1 else 0 end is_part_of_pk from USER_TAB_COLUMNS col where table_name = :some_table_name order by column_id; ...

Do I Generate a :primary_key to Create Headers in Table?

ruby-on-rails,ruby,table,primary-key,scaffold

No :primary_key was needed I used scope in the rb file instead.

Why is it necessary for each and every Table to have a Primary Key?

c#,sql-server,entity-framework,primary-key

why isn't it possible for EntityFramework to work with tables without primary keys? That was a design decision. Entity Framework doesn't want to do updates when the uniqueness of a row cannot be determined. They could have decided to support it, for example by adding where oldcolN=oldvalN for all...

Can not Insert the NULL value into Auto Increment PrimaryKey Column

c#,sql,sql-server,primary-key

NOT NULL means you can't insert NULL into those columns you need to explicitly insert it. Setting IDENTITY(1,1) will insert item_id automatically by seed of 1. Change your definition like this CREATE TABLE [dbo].[Items] ( [item_id] INT NOT NULL IDENTITY(1, 1), [item_name] VARCHAR (50) NULL, [item_model] VARCHAR (50) NULL, [item_price]...

In MongoDB, how likely is it two documents in different collections in the same database will have the same Id?

mongodb,primary-key,uniqueidentifier

Short Answer for your question is : Yes that's possible. below post on similar topic helps you in understanding better: Possibility of duplicate Mongo ObjectId's being generated in two different collections?...

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

Your table Autocare has a compound primary key made up from two columns: PRIMARY KEY CLUSTERED ([IDAutocar] ASC, [IDTipAutocar] ASC), Therefore, any table that wishes to reference Autocare must also provide both columns in their foreign key! So this will obviously not work: CONSTRAINT [FK_Curse_Autocare] FOREIGN KEY ([IDAutocar]) REFERENCES [Autocare]([IDAutocar])...

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

json,django,primary-key,django-rest-framework,nested-lists

After some digging, I found that the read_only fields are only for output presentation. You can find the similar question on the offcial github link of Django REST Framework. So the solution is overriding the read_only field in the serializer as follow: class ProductlistSerializer(serializers.ModelSerializer): productlist_id = serializers.IntegerField(read_only=False) class Meta: model...

C# Auto Increment Primary Key with String?

c#,sql,combobox,primary-key,auto-increment

The actual cause of the issue you are having is because you are adding to a new EmployeeManager's combobox. You need to pass a reference to your form into your EmpPrimary ... EmpPrimary(this); //in your forms form load public void empPrimary(EmployeeManager form){ //change the method parameters form.cmbEmployeeID.Items.Add(empPK); // Now use...

Replacing values with an ID when importing tables in MS Access

sql,csv,primary-key,jet,ms-access-2013

Since you can't JOIN on Memo fields in Access, you will need to create a CROSS JOIN and filter where the ID's are equal. From there, it's just an UPDATE: UPDATE Products, Notes SET Products.NoteID = Notes.NoteID WHERE (Products.Notes=Notes.Notes); sgeddes has the same idea in his code as well....

Create an index, or add to the primary key and create a new table?

database,database-design,cassandra,nosql,primary-key

As noted above, a well-grounded answer can only be given if you provide more information about the cardinality of the a, b and c columns. Also make sure you understand the meaning of partitioning key and clustering key - they are both part of primary key, and have a huge...

Update even though using constraints

sql-server,sql-update,foreign-keys,primary-key,sql-server-2014

Enable update cascade on the foreign key ALTER TABLE t_Table_Language DROP CONSTRAINT FK_t_Table_Language_t_Table ALTER TABLE t_Table_Language ADD CONSTRAINT FK_t_Table_Language_t_Table FOREIGN KEY (funID) REFERENCES t_Table(funID) ON UPDATE CASCADE EDIT: Or the other way around, i'm not sure which table has the foreing key ALTER TABLE t_Table DROP CONSTRAINT FK_t_Table_Language_t_Table ALTER TABLE...

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

mysql,database,primary-key

It's not necessary to have an integer ID, although it is common. Having a country name as key will result in a large key, with (sometimes) spaces or special characters in it. This means that using them is slower, and if you use them in, say, an HTML/JavaScript application, you...

Table is referenced by a constraint in table SYS_REF_SYS_FK_272_275 table: STAFF2 in statement [drop table STAFF]

sql,constraints,primary-key,hsqldb,drop-table

You can drop the tables but you have to do it in reversed order. STAFF2 depends on STAFF so you cannot drop STAFF first. You don't have to remove constraints separately. Just execute following two commands: DROP TABLE STAFF2; DROP TABLE STAFF; ...

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

mysql,database,foreign-keys,primary-key

That can't be implemented as a FOREIGN KEY constraint in InnoDB; the datatype of the foreign key column(s) must match the datatypes of the referenced column(s) EXACTLY. You can perform a join operation on the columns, although there's going to need to be datatype conversion on one side or the...

Cassandra select on primary key without primary keys name

primary-key,cql3,cassandra-2.0

Ukemi, I'm not aware of this being possible because a Cassandra table can have a compound primary key and the first part of the primary key can also itself be a compound partition key. E.g. in this situation: CREATE TABLE .... PRIMARY KEY ((a, b), c, d) .. Does <PK>...

Auto generate primary key as current date time in H2 database

sql,database,timestamp,primary-key,h2

You can try this in the H2 console: call formatdatetime(now(),'yyyyMMddHHmmssSSS'); This will give you a properly formatted string. Now you need to convert it to bigint. call cast(formatdatetime(now(),'yyyyMMddHHmmssSSS') as bigint); Last step: change your SQL accordingly... CREATE TABLE TEST_TABLE( ID BIGINT DEFAULT CAST(FORMATDATETIME(CURRENT_TIMESTAMP(), 'yyyyMMddHHmmssSSS') AS BIGINT) PRIMARY KEY, NAME VARCHAR(255)...

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

sql,sql-server,database,for-loop,primary-key

This query, will exclude all tables that have either a primary key or an identity column, it will then add identity column and primary key on the remaining objects DECLARE @PKScript AS VARCHAR(max) = ''; SELECT @PKScript += ' ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(obj.SCHEMA_ID))+'.'+ QUOTENAME(obj.name) + ' ADD PK_ID BIGINT...

SQL Unique Key Violation

sql-server,primary-key

Run this query: SELECT tc.Constraint_Name, cc.Column_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc ON tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME WHERE tc.CONSTRAINT_NAME = 'IX_InternalLocation' AND tc.CONSTRAINT_TYPE = 'Unique' against your database. You should get as result: Constraint_Name Column_Name ----------------------------------------------------- IX_InternalLocationDateMexri LocationID IX_InternalLocationYpallhlosId Name which means there is a UNIQUE INDEX placed on...

Sql Lite Primary key for Application

android,sqlite,primary-key

long is a 64-bit type, but there are more than 264 possible application IDs. This is not possible. Just use the string as primary key....

SQL Ignore duplicate primary keys

sql,sql-server,tsql,primary-key

You stated that no matter which row to choose when there are more than one row for the same Id, you just want one row for each id. The following query does what you asked for: DECLARE @T table ( id int, name varchar(50), address varchar(50) ) INSERT INTO @T...

Add serial value across multiple tables

sql,postgresql,database-design,primary-key,common-table-expression

1) Fix your design CREATE TABLE usr ( usr_id serial PRIMARY KEY, ,login text UNIQUE NOT NULL ); CREATE TABLE userdata ( usr_id int PRIMARY KEY REFERENCES usr ,password text NOT NULL ); Start by reading the manual about identifiers and key words. user is a reserved word. Never use...

Wheres my Mistake?

java,database,prepared-statement,primary-key,sqlexception

Try SELECT last_insert_rowid() FROM MitarbeiterInfo instead. The exception you're getting seems to imply that IDENTITY_VAL_LOCAL() is not supported by SQLite....

primary and foreign keys in postgresql - total beginner

postgresql,foreign-keys,primary-key

One entity that is missing from your list is branch (of the company). The table definitions are then: CREATE TABLE country ( id integer NOT NULL PRIMARY KEY, name varchar NOT NULL, population integer NOT NULL ); CREATE TABLE city ( id integer NOT NULL PRIMARY KEY, name varchar NOT...

Using Hibernate Get with Multi-Column Primary Key

java,hibernate,primary-key,composite-primary-key

Try to use and @IdClass or @EmbeddedId public MyJoinClass implements Serializable { private static final long serialVersionUID = -5L; @EmbeddedId private MyJoinClassKey key; } public MyJoinClassKey implements Serializable{ @Column(name = "ID") private long id; @Column(name = "EMAIL_ADDRESS_ID") private long emailAddressId; } Then use MyJoinClass a = (MyJoinClass )session.get(MyJoinClass .class, new...

Joins etc. with GUID as Primary Key on SQL Server

sql-server,database-design,primary-key,guid

There is no point in creating a clustered index on a monotonically increasing numeric field in your table unless you're planning on using that field to access the data. I suggest you read this post on DBA.SE for a good discussion of clustered and non-clustered primary keys....

MySQL: Is it mandatory to create a `Unique Key` when you have 2 `Primary Keys` in a table?

mysql,sql,database,primary-key,unique-key

You do not ever have two primary keys in a single table, you have one by definition. Your requirements seem to be: unique ID in the transaction table. unique ID in the fee table. up to one fee per transaction. What you do in that case is create primary keys...

SQL copying record with out specifying column list; ignoring Identity

sql,sql-server,copy,primary-key,identity

Use Dynamic SQL: get your list of columns (except ID), build an insert statement using that list, and then call exec on it: SELECT * INTO #tmp FROM TableA WHERE Id = 1; ALTER TABLE #tmp DROP COLUMN id; DECLARE @cols varchar(max); SELECT @cols = COALESCE(@cols + ',', '') +...

SQL Error when creating a table

mysql,sql,database,primary-key

Try this 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 , primary key (id) //specify id as primary key will sort out the error..try...

Primary keys with same names in different tables

sql,foreign-keys,primary-key

The theory is a bit off on the Event Plan... Given that a plan can be used for any number of events but an event always has only 1 plan... I'd structure like this: PLAN PID, PName.... etc.., Event EID, Semester, MID, SID, PID, Time, Room Module MID, Mname... Subject...

Database Table Primary Keys

database,datatable,primary-key

When you make a 'join table' the primary keys from each contributing table, form a composite key for the join table. It is then quite possible that this composite key can be indexed. Inefficient indexing strategies can degrade performance. An example is the 'InnoDB' engine for MySQL, this is one...

Delete single column from primary key in mysql

mysql,duplicates,primary-key,multicolumn

I don't care which duplicate survives, I just want my primary key to be correct If your new PK consists of columns 1 through 4, and columns 1 through 5 are unique at the moment, you can can use SELECT column1, column2, column3, column4, MIN(column5) FROM mytable GROUP BY...

Design a table structure

mysql,database,primary-key

A primary key is a unique identifying key, it can't be duplicated no matter what, if you want another duplicate key then add your own extra field --------------------------------------------------- | pk | my key | fact_name | department | subjects | --------------------------------------------------- | 1 | 1 | ABC | 1 |...

create primary key on existing table without primary key in SQL Server

sql,sql-server,primary-key

To add a primary key to an existing table you can do this: ALTER TABLE yourTableName ADD yourColumnName INT NOT NULL PRIMARY KEY IDENTITY(1, 1) which will add a new column and automatically populate it with unique integer values starting from one. To add a foreign key to another table...

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

ios,parse.com,primary-key,foreign-key-relationship

The objectId created by parse is the unique identifier. There is no concept of a primary key from the point of view of indexing, just unique identification. A pointer or relationship is the approach you should use to foreign key linking. This is effectively a link to the foreign object...

GeneratedValue counter for Id resets every time that the server and client are executed

jpa,eclipselink,primary-key,generated

The code: @Id @GeneratedValue private int cod_utente; doesn't set a specific stategy to generate the values for the ID. It is the same as this code: @Id @GeneratedValue(strategy=GenerationType.AUTO) private int cod_utente; GenerationType.AUTO means that the persistence provider (in Glassfish the default persistence provider is EclipseLink) should choose an appropriate strategy...

HSQLDB error on primary key definition

sql,syntax-error,primary-key,hsqldb

You need a data type for the column, plus the primary key keyword goes to the end (as documented in the manual) country_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL PRIMARY KEY you are also missing a comma after the column definition in...

Custom model field change base of id

django,primary-key,default

You could either: Override your model's save() method: class YourModel(models.Model): base_36 = models.TextField() def save(self, *args, **kwargs): # will assign a value to pk super(YourModel, self).save(*args, **kwargs) # convert the pk to base 36 and save it in our field self.base_36 = convert_base(self.pk, 36) self.save() Turn your base_36 field into...

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

mysql,sql,database,foreign-keys,primary-key

Possible duplicate: MySQL: How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys? A possible solution is: USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'your_table' AND REFERENCED_COLUMN_NAME = 'reference_number'; ...

DB2 insertion into a table with identity column

db2,primary-key,sql-insert

In order to skip over a column in an INSERT statement, you must specify the columns that are not to be skipped: db2 "insert into TRAINING1 (f1, name, surname) values ('hello', 'world', 'foo')"...

Should this two tables be related? [closed]

php,mysql,foreign-keys,primary-key,innodb

Using foreign keys it is up to you, it is not mandatory, it provides you with some functionality such as: Make sure that you don't get a row inserted with a invalid created_by When you delete an account, "if" you want, you can automate the news deletion so you don't...

Automatically generate Date + 4-digit sequence number for ID in Access 2010+

date,ms-access,ms-access-2010,primary-key,auto-generate

Since you are using Access 2010+ the best way to accomplish your goal would be to use a Before Change data macro like this To create the Before Change macro, click the "Before Change" button on the "Table" tab of the ribbon when the table is open in Datasheet View:...

Can prime attributes depend on each other?

database,primary-key,normalization,composite-key

In any relation with multiple candidate keys, there'll inevitably be cyclic dependencies between them - since any candidate key can uniquely identify each tuple and each tuple has a value for each candidate key. The fact that one key is selected as being "more equal" than the others and named...

Database Design - Foreign Key and Primary Key relationships across 3 tables

sql,sql-server,database,database-design,primary-key

I recommend you check out creating and altering CHECK constraints. a simple condition of (Client_ID IS NOT NULL OR Site_ID IS NOT NULL) As a side note, the structure should work for the business rules. Can a Client have a contract with without a site to work at or on?...

How predictable is NEWSEQUENTIALID?

sql,sql-server,primary-key,guid,newsequentialid

The underlying OS function is UuidCreateSequential. The value is derived from one of your network cards MAC address and a per-os-boot incremental value. See RFC4122. SQL Server does some byte-shuffling to make the result sort properly. So the value is highly predictable, in a sense. Specifically, if you know a...

Should I create a table with multiple candidate keys?

database,database-design,primary-key,functional-dependencies

If this is what the business situation is like, and this solution accurately refelcts the business scenario, then what could possibly be "bad" about it ? EDIT I assumed you were aware that such designs might get you into problems for certain "swapping" updates, but perhaps it would have been...

Insert row into table with only a primary key field

sql,postgresql,primary-key,auto-increment,sql-insert

You should create a sequence and set the default as nextval like so: create sequence plan_sequence start 100 increment 1 ; create table plan ( id int4 not null default nextval('plan_sequence'), primary key (id) ); A sequence provides an auto-incrementing value. It is used for primary keys, etc....

1 auto_increment with 2 primary keys

mysql,primary-key,innodb,auto-increment,myisam

BEGIN; SELECT @id := MAX(id)+1 FROM foo WHERE other = 123 FOR UPDATE; INSERT INTO foo (other, id, ...) VALUES (123, @id, ...); COMMIT; ...

MySql - Is primary key unique by default?

mysql,primary-key,unique-key

Primary key is always unique in every SQL. You dont have to explicitly define it as UNIQUE. On a side note: You can only have onePrimary key in a table and it never allows null values. Also you can have only one primary key constraint in the table(as the point...

Adding a primary key to a lookup table

sql-server,entity-framework-5,primary-key

In pure SQL terms that concatenated key is perfect. But there are other non-SQL considerations for a PK as you mentioned. I have run into issues with various software you can use to build your automated scripts for database deployment. The main issue I run into is if no PK...

MySQL Modeling: Relationship normalization & ring issue

mysql,relational-database,primary-key,relationship

To answer your first question, 'ring relationships' as you refer to them are neither incorrect or correct, but do hint at a complex relationship. In the relationships you describe, and movie 'could have' one or more codecs and a codec 'could have' one or more episodes. Assuming that our movie...

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

database,foreign-keys,relational-database,primary-key,flask-sqlalchemy

In short - yes. Having the same field as a primary key and a foreign key is used to create a 1:0..1 relationship. A user may have a single record of details, but cannot have multiple records of details, and you cannot have details for users that do not exist...

Optimizing chat application database

sql-server,indexing,chat,primary-key

Yes, you should define a primary key clustered index on the ID column. Yes, you should define a nonclustered composite index on (User1, User2, Date). ID is already "included" in the nonclustered index if you define ID as the clustered index....

MySQL Insert Statement foreign key auto add pk

php,mysql,foreign-keys,primary-key,sql-insert

You can do it all in MySQL by using a SELECT statement to provide the values for the INSERT. For example: INSERT INTO package (master_id, library_id) SELECT (SELECT master_field FROM master_table WHERE {some condition} LIMIT 1), (SELECT library_id FROM library_table WHERE {some condition} LIMIT 1) You might want to not...

C# Datagridview don't show primary key

c#,sql,gridview,primary-key

Just hide it in the DataGridView. Either by metroGrid1.Columns[indexOfPrimaryKey].Visible = false; or by checking the names of the columns and hiding those you do not want....

Msg 2627 Violation of PRIMARY KEY constraint

mysql,sql,database,duplicates,primary-key

your problem is here primary key (teams)); i guess you have to do it like that primary key (setId)); like that: create table tblShowteam( SetId int, datum date, teams int, primary key (setId)); because you are inserting same teams 1 while you are using teams as primary key which means...

Unique key parts not showing in Create Form Grails

grails,primary-key,unique

In Grails, version is a special property of Domain classes used for optimistic locking. Rename your property to something other than version. You can read more about this in the user guide.

SQL request foreign key on same table

sql,select,foreign-keys,primary-key

Welcome to Stack Overflow! Try this: SELECT a.ID_Employee, a.NAME_Employee, b.ID_Employee AS ManagerID, b.Name_Employee AS Manager FROM T a INNER JOIN T b ON a.ID_Manager = b.ID_Employee ...

How to get Primary Key on SQL Update?

sql,tsql,primary-key

You should use the OUTPUT cluase. Books online will explain how.

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

sql,sql-server,primary-key

I was looking for a similar answer to this question. However it was a question of storing a GUID as binary or varchar in a database. Different type of object but same principle. Here is some more information: How should I store GUID in MySQL tables? I also found a...

Foreign key join on 3 tables

php,mysql,foreign-keys,inner-join,primary-key

You don't have to use WHERE clause. What you need is fix your JOIN and make it JOIN the tablec with the condition TableA.TableC_ID = TableC.ID instead, like this: SELECT TableC.value1,TableB.value2 FROM TableA JOIN TableB ON TableB.ID = TableA.ID JOIN TableC ON TableA.TableC_ID = TableC.ID WHERE tableA.ColumnA = 'val2'; ...

Select 3 Distinct columns return 5 columns

sql,sql-server,primary-key

I use the ROW_NUMBER function https://msdn.microsoft.com/en-us/library/ms186734.aspx: Here is what your SQL should look like: SELECT DISTINCT d.[DoctorId] , d.[Plan] , d.[StartDate] , d.[EndDate] , d.[Status] FROM DoctorContracts as d INNER JOIN (SELECT [GROUP] , [STARTDATE] , ROW_NUMBER() OVER(PARTITION BY [GROUP] ORDER BY [STARTDATE] ASC) AS Row FROM DoctorContracts) as t...

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

ruby-on-rails,postgresql,activerecord,primary-key

Create a migration: bundle exec rails generate migration ChangeSerialToBigserial Edit migration file: def up execute "ALTER TABLE my_table ALTER COLUMN id SET DATA TYPE bigint" end Login to the rails dbconsole(it is just psql console only with chosen a database for your app): bundle exec rails dbconsole Check table: #...

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

mysql,sql,database,performance,primary-key

No. 2 and 3 should not be different performance-wise. If you notice a difference then it could be retrieving from cache and has nothing to do with the query. Except of course when you send the resultset to a client(browser), in which case you want to minimize the amount of...

How does using char(17) as primary key to store VIN numbers in a table with SQL Server affect performance? [closed]

sql-server,join,primary-key,sqldatatypes,vin

Just a personal opinion.. I always use int as a primary key. Primary key is in most cases always a clustered index. It's 4 bytes vs. 17 bytes and you can always put a non-clustered index on your VIN column. Keep things simple and clear. It's just my opinion though....

PostgreSQL: Primary Keys

postgresql,primary-key

In the second case, the combination of a and c is the one and only primary key of DB table example. That means that a may not be unique, c may not be unique but the combination of a and c should be unique per row. In that sense there...

Show primary key column in table

mysql,sql,database,primary-key

Simply add required columns in your query comma separated, SELECT Recipes.RecipeID,Recipes.RecipeClassID,Recipes.RecipeTitle FROM Recipes ...

Cannot add foreign key constraint in MySQL

mysql,sql,foreign-keys,primary-key

You need to create the user table first

postgresql primary key constraint on encrypted data using pgcrypto extension

database,postgresql,constraints,primary-key,pgcrypto

If you wish to enforce uniqueness your best bet would be to hash your information and store that hash in a separate column with a unique index, I often use digests to check for uniqueness on longer text purely because of the small space requirements it has, however due to...

Penalties for INSERT with existing primary key?

python,postgresql,primary-key

The foolproof way to do it at the moment is try the insert and let it fail. You can do that at the app level or at the Postgres level; assuming it's not part of a procedure being executed on the server, it doesn't materially matter if it's one or...

Integrity Error in Django 1.7

python,django,orm,primary-key

If you call update_or_create with a changed "about" value and an existing primary key, this must throw an error. In case you want to do this you should pass your updated values to the defaults argument like shown in the docu: obj, created = Person.objects.update_or_create( first_name='John', last_name='Lennon', defaults=updated_values) ...

mysqli_result — Need a string value from result

php,mysql,database,mysqli,primary-key

if(!$primaryKey = $link->query("SHOW INDEX FROM ".$tables[$x])) { die("Error! [" . $link->error . "]"); } $result = $primaryKey->fetch_assoc(); $result should contain the index...