How to create concatened 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?
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
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
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
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
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
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
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'
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?
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
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
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
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
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
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
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
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?
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
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
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?
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)?
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
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
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?
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
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...